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
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'
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'
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'
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
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
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)
