update多表联合更新_update 多表

t_student表和t_class表

Mysql

UPDATE db_shop.t_student s,db_shop.t_class c 
SET s.class_name=c.name,c.stu_name=s.name 
WHERE s.class_id=c.id
--等效于
UPDATE db_shop.t_student s JOIN db_shop.t_class c 
SET s.class_name=c.name,c.stu_name=s.name 
WHERE s.class_id=c.id
1
2
3
4
5
6
7

UPDATE db_shop.t_student s JOIN db_shop.t_class c ON s.class_id=c.id 
SET s.class_name='test11',c.stu_name='test11'
1
2

UPDATE db_shop.t_student s LEFT JOIN db_shop.t_class c ON s.class_id=c.id 
SET s.class_name='test22',c.stu_name='test22'
1
2

UPDATE db_shop.t_student s RIGHT  JOIN db_shop.t_class c ON s.class_id=c.id 
SET s.class_name='test33',c.stu_name='test33'
1
2

Postgresql

在update语句中不应该通过join来进行多表关联，而是要通过from来多表关联
对于set xxx = 'xxx'这个update的部分，是不可以在column字段前加上表前缀的
2表

update db_shop.t_student 
set class_name =c.name
from db_shop.t_class c
where class_id=c.id
1
2
3
4

3表或更多表

UPDATE table1 SET mzdm=t.code_id FROM(
SELECT card_id,code_id 
FROM table2 t2 INNER JOIN table3 t3 on t2.nation=t3.nation 
) t WHERE t.id=table1.id
1
2
3
4

Oracle

Oracle语法: UPDATE updatedtable SET (col_name1[,col_name2…])= (SELECT
col_name1,[,col_name2…] FROM srctable [WHERE where_definition])

Oracel 示例: update db_shop.t_student s set (s.class_name)= (select c.name from db_shop.t_class c where s.class_id=c.id)