使用 case 语句 (MYSQL) 根据另一个表中的值更新一个表 [英] Update one table based on values in another table using case statement (MYSQL)

问题描述

我正在尝试运行以下查询以使用 case 语句根据另一个表中的值更新表.我收到以下错误.

I am trying to run the below query to update a table based on values in another table using case statement. And i get the following error.

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from tbl_a as a, tbl_b as b
  WHERE a.DIV_NO = b.' at line 12

这是查询:

UPDATE a
  SET
    a.MIN_5 = b.Min_Five,
    a.IN_PR = b.Ad_Flag ,
    a.FRC = (
                    CASE
                       WHEN b.Prc_Fl = 'N' THEN 'Y'
                       WHEN b.Prc_Fl = 'Y' THEN 'N'
                       ELSE 'D'
                    END
            )
  FROM tbl_a AS a, tbl_b AS b
  WHERE a.DIV_NO = b.Div_num
    AND a.OFR_ID = b.Flg ;

推荐答案

用 JOIN

UPDATE 
  tbl_a AS a
  JOIN   
  tbl_b AS b ON (a.DIV_NO = b.Div_num AND  a.OFR_ID = b.Flg) 
  SET
  a.MIN_5 = b.Min_Five,
  a.IN_PR = b.Ad_Flag,
  a.FRC = 
    CASE
      WHEN b.Prc_Fl = 'N' 
      THEN 'Y' 
      WHEN b.Prc_Fl = 'Y' 
      THEN 'N' 
      ELSE 'D' 
    END

这篇关于使用 case 语句 (MYSQL) 根据另一个表中的值更新一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！