update delete使用join
时间：2010-04-14 来源：ubuntuer
1. update join
UPDATE TABLE_1 LEFT JOIN TABLE_2 ON TABLE_1.COLUMN_1= TABLE_2.COLUMN_2
SET TABLE_1.COLUMN = EXPR WHERE TABLE_2.COLUMN2 IS NULL
UPDATE Groups LEFT JOIN GroupSum USING (GroupId)
SET ValWithinGroup=ValWithinGroup-baseVal; 三表
update Table1 t1
join Table2 t2 on t1.ID=t2.t1ID
join Table3 t3 on t2.ID=t3.t2ID
set t1.Value=12345
where t3.ID=54321 2. delete join For example, to delete rows that exist in t1 that have no match in t2, use a LEFT JOIN:
DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
UPDATE TABLE_1 LEFT JOIN TABLE_2 ON TABLE_1.COLUMN_1= TABLE_2.COLUMN_2
SET TABLE_1.COLUMN = EXPR WHERE TABLE_2.COLUMN2 IS NULL
UPDATE Groups LEFT JOIN GroupSum USING (GroupId)
SET ValWithinGroup=ValWithinGroup-baseVal; 三表
update Table1 t1
join Table2 t2 on t1.ID=t2.t1ID
join Table3 t3 on t2.ID=t3.t2ID
set t1.Value=12345
where t3.ID=54321 2. delete join For example, to delete rows that exist in t1 that have no match in t2, use a LEFT JOIN:
DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
相关阅读 更多 +
