SQL 基础语法整理

update select 之后的数据

update B set B_account = b.s_amountfrom B a,(    select sum(amount) as s_amount,pay_id from A group by pay_id) bwhere a.pay_id = b.pay_idselect name from (select name,avg(score) as a_score from student where name like '张%' group by name) a where a.a_score > 75

可以看出Mysql、Sybase、SQL SERVER的解决办法是使用UPDATE…SET…FROM…WHERE…的语法，实际上就是从源表获取更新数据。
注意：from后面是两个表的连接，源表+查询结果表。

oracle、DB2中：

UPDATE A SET (A1, A2, A3) = (SELECT B1, B2, B3 FROM B WHERE A.ID = B.ID)WHERE ID IN (SELECT B.ID FROM B WHERE A.ID = B.ID)UPDATE A SET (A1, A2, A3) = (SELECT B1, B2, B3 FROM B WHERE A.ID = B.ID)

可以看出，后面的查询只是查询的一张表。

注意：mysql 5.7中上面语句不好使，要这样写：

update users a left join employee b on a.users_id = b.id set a.salary = b.salary;

注意：

update table_name set col = '' where id in (select id from table_name)

这种写法不对，mysql表示不能载select出一个表数据后，又update这个表数据，比如：

update task_relation set depend_type = 1 where id in (    select task_relation.id      from task c      join task_relation        on c.id = task_relation.child_id      join task p        on p.id = task_relation.parent_id     where c.task_name like '%horigindb%'       and p.task_name like '%hbinlog%'       and task_relation.depend_type = 0       and c.status = 0)

可以在update和select之间加入一个中间表

update task_relation   set depend_type = 1 where id in (        select t.id          from (                select task_relation.id                  from task c                  join task_relation                    on c.id = task_relation.child_id                  join task p                    on p.id = task_relation.parent_id                 where c.task_name like '%horigindb%'                   and p.task_name like '%hbinlog%'                   and task_relation.depend_type = 0                   and c.status = 0               ) t       )

或者改变写法：
比如：

update task_relation  join task c    on c.id = task_relation.child_id  join task p    on p.id = task_relation.parent_id   set task_relation.depend_type = 1 where c.task_name like '%horigindb%'   and p.task_name like '%hbinlog%'   and task_relation.depend_type = 0   and c.status = 0

对于delete，也需要注意上面这点，同样的解决办法：

delete task_relation from task_relation  join task c    on c.id = task_relation.child_id  join task p    on p.id = task_relation.parent_id   set task_relation.depend_type = 1 where c.task_name like '%horigindb%'   and p.task_name like '%hbinlog%'   and task_relation.depend_type = 0   and c.status = 0

update多个字段

Mysql中：

update @A set c1=b.c1 ,c2=b.c2,c3=b.c3from @A a,@B b where a.id=2 and b.id=6