多表删除和更新

多表删除

-- 将从表 t1 里把其 id 值可以在另一个表 t2 里找到的所有行全部删掉
DELETE t1 FROM t1 INNER JOIN t2 ON t1.id = t2.id;
-- 从两个表里把 id 值相匹配的行都删除掉
DELETE t1,t2 FROM t1 INNER JOIN t2 ON t1.id = t2.id;
-- 从 t1 里把表 t2 中没有匹配的行都删除掉
DELETE t1.* FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.id IS NULL;
-- 使用 USING 子句来连接这些表
DELETE FROM t1 USING t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.id IS NULL;

多表更新

-- 为每位学生的考试成绩都加上一分
UPDATE score,grade_event SET score.score = score.score+1
WHERE score.event_id = grade_event.event_id
AND grade_event.data='2018-01-10' AND grade_event.category='Q';
-- 使用单表更新和子查询
UPDATE score SET score = score+1
WHERE event_id = (
  SELECT event_id FROM grade_event
  WHERE data='2018-01-10' AND category = 'Q'
);
-- 把 id 列值相同的各行从 t1.a 列复制到 t2.a 列
UPDATE t1,t2 SET t2.a = t1.a WHERE t1.id = t2.id;