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'
);
总结: 个人建议第三种,效率高