MySQL之UPDATE与SELECT结合语法详解与实例

第一种：
语法：

UPDATE table1 SET column1 = (SELECT column FROM table2 [WHERE condition])
WHERE table1.column2 = value;

注：若不加where条件则是更新表中的所有数据，
故执行没有where子句的update要慎重再慎重。

第二种：
语法：

UPDATE table1 INNER/LEFT/RIGHT JOIN 
table2
/
(
 SELECT COLUMNS FROM table3 
 [INNER/LEFT/RIGHT JOIN ON CONDITION] 
 [WHERE conditions]
) AS t3
ON CONDITION
SET column1 = value1,column2 = value2,...
[WHERE conditions];

实例1：

UPDATE 
  $ table1 a 
  INNER JOIN $ table2 b 
    ON a.user_id = b.user_id 
    SET a.balance = a.balance + b.income,
  b.status = 1 
WHERE b.id = 1 
  AND b.status = 0;

实例2：

UPDATE 
  A 
  INNER JOIN 
    (SELECT 
      B.B1 AS B1,
      B.B2 AS B2,
      C.C1 AS C1 
    FROM
      B 
      LEFT JOIN C 
        ON B.B3 = C.C3) AS t 
    ON A.A3 = t.B1 SET A.A1 = t.B2,
  A.A2 = t.C1;