最常用的update语法是:
UPDATE
<
table_name
>
SET < column_name1 > = < value > , SET < column_name2 > = < value >
SET < column_name1 > = < value > , SET < column_name2 > = < value >
如果我的更新值Value是从一条select语句拿出来,而且有很多列的话,用这种语法就很麻烦
第一,要select出来放在临时变量上,有很多个哦
第二,再将变量进行赋值。
列多起来非常麻烦,能不能像Insert那样,把整个Select语句的结果进行插入呢?就好象下面
insert
into
table1
(c1, c2, c3)
( select v1, v2, v3 from table2)
(c1, c2, c3)
( select v1, v2, v3 from table2)
答案是可以的,具体的语法如下:
UPDATE
<
table_name
>
<
alias
>
SET ( < column_name > , < column_name > ) = (
SELECT ( < column_name > , < column_name > )
FROM < table_name >
WHERE < alias.column_name > = < alias.column_name > )
WHERE < column_name > < condition > < value > ;
SET ( < column_name > , < column_name > ) = (
SELECT ( < column_name > , < column_name > )
FROM < table_name >
WHERE < alias.column_name > = < alias.column_name > )
WHERE < column_name > < condition > < value > ;
注意:最后面的括号后面一定是查询才可以。