[636]mysql update多表联合更新

最新推荐文章于 2024-07-17 04:38:08 发布

周小董

最新推荐文章于 2024-07-17 04:38:08 发布

阅读量429

点赞数

分类专栏：数据库&中间件

本文链接：https://blog.csdn.net/xc_zhou/article/details/95680189

版权

数据库&中间件专栏收录该内容

123 篇文章 13 订阅

订阅专栏

下面我建两个表，并执行一系列sql语句，仔细观察sql执行后表中数据的变化，很容易就能理解多表联合更新的用法

执行 UPDATE student s,class c SET s.class_name='test00',c.stu_name='test00' WHERE s.class_id = c.id
执行UPDATE student s JOIN class c ON s.class_id = c.id SET s.class_name='test11',c.stu_name='test11'
执行 UPDATE student s LEFT JOIN class c ON s.class_id = c.id SET s.class_name='test22',c.stu_name='test22'
执行 UPDATE student s RIGHT JOIN class c ON s.class_id = c.id SET s.class_name='test33',c.stu_name='test33'
执行 UPDATE student s JOIN class c ON s.class_id = c.id SET s.class_name=c.name , c.stu_name=s.name

6、其它类似sql

UPDATE b SET b.memo = a.name FROM a, b WHERE a.id = b.id
UPDATE b SET b.memo = a.name FROM b LEFT JOIN a ON a.id = b.id
UPDATE A  SET A1 = B1, A2 = B2, A3 = B3  FROM A LEFT JOIN B ON A.ID = B.ID
UPDATE A SET A1 = B1, A2 = B2, A3 = B3 FROM A, B WHERE A.ID = B.ID

参考：https://blog.csdn.net/u012604745/article/details/80642015
https://blog.csdn.net/lxrj2008/article/details/79457804