mysql 单表更新和多表更新

mysql更新语句sql

一、单表更新:

UPDATE table  name
SET name.column_name = new_value
WHERE name.column_name = some_value;

二、多表更新:

1.级联更新
UPDATE product p, product_price pp  
SET pp.price = pp.price * 0.8  
WHERE p.productId = pp.productId  
AND p.dateCreated < '2014-01-01';

2.使用inner Join 或者 left Join更新
UPDATE product p  
INNER JOIN product_price pp  
ON p.productId = pp.productId  
SET pp.price = pp.price * 0.8  
WHERE p.dateCreated < '2014-01-01';

UPDATE product p  
LEFT JOIN product_price pp  
ON p.productId = pp.productId  
SET pp.price = pp.price * 0.8 
WHERE p.dateCreated < '2014-01-01';

3.关联子查询
UPDATE product_price pp
SET pp.price = pp.price * 0.8
WHERE
    pp.productId IN (
        SELECT
            p.productId
        FROM
            product p
        WHERE
            p.dateCreated < '2014-01-01'
    );

总结： 个人建议第三种，效率高