SQL中update与update select结合语法详解与实例

1、通用update

一般简单的update语法比较通用

update table_nameSET column1=value1,    column2=value2,    ...WHERE some_column = some_value;

注:若不加where条件则是更新表中的所有数据,
故执行没有where子句的update要慎重再慎重。

update subjectSET name='数学',    type='理学' WHERE id = 1;

2、GreenPlum update与select结合

语法:

update [only] table [[as] alias] SET {column = {expression | default} |    (column [, ...]) = ({expression | default}[, ...])}[, ...][FROM fromlist][WHERE condition | WHERE current of cursor_name];

注:若不加where条件则是更新表中的所有数据,
故执行没有where子句的update要慎重再慎重。

实例:

update table1 t1SET column1 = t2.columnname1    column2 = t2.columnname2 FROM (select columnname1,columnname2 from table2) t2 WHERE t1.column3 = t2.column3 AND t1.column = '111';

注:对于set列中左边的列不能使用t1.这种别名方式,只能使用column名称

PostgreSQL与GreenPlum语法基本一致

3、MySQL update与select结合

第一种:

语法:

update table1 SET column1 = (select column FROM table2 [WHERE condition])WHERE table1.column2 = value;

注:若不加where条件则是更新表中的所有数据,
故执行没有where子句的update要慎重再慎重。

实例:

update tb_bookcaseSET name = (select bookname FROM tb_bookinfo WHERE tb_bookinfo.type = tb_bookcase.type            AND tb_bookinfo.ord_date IN (select MAX(ord_date) FROM tb_bookinfo))WHERE tb_bookcase.subject = '理学';

第二种:

语法:

update table1 inner/left/right join table2/(select columns from table3           [inner/left/right join on condition]  [where conditions]) as t3ON conditionSET column1 = value1,column2 = value2,...[WHERE conditions]; 

实例:

例1:

update $table1 a INNER JOIN $table2 bON a.user_id = b.user_id SET  a.balance = a.balance + b.income,b.status= 1 WHERE  b.id = 5 AND b.status = 0;

例2:

update A INNER JOIN     (select B.B1 as B1,B.B2 as B2,C.C1 as C1 from B LEFT JOIN C on    B.B3=C.C3) as tON A.A3=t.B1set A.A1=t.B2,    A.A2=t.C1;

update tb_bookcase INNER JOIN     (select tb_bookinfo.rid as rid,tb_bookinfo.bookname,department.name LEFT JOIN department on    tb_bookinfo.depid=department.id) as tON tb_bookcase.id=t.ridset tb_bookcase.bookname=t.bookname,    tb_bookcase.departname=t.name ;

4、Oracle update与select结合

语法:

update table1 aliasSET (column_name,column_name ) = (        select column_name, column_name FROM table2 WHERE table2.column_name = alias.column_name)[WHERE column_name = VALUE]

注:若不加where条件则是更新表中的所有数据,
故执行没有where子句的update要慎重再慎重。

实例:

例1:

update t_order t1SET (ordername,orderprice) = (select detailname,totalprice FROM t_detail WHERE t_detail.detailclasses = t1.classes)WHERE t1.orderid = 1  

例2:

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)

5、SQLServer update与select结合

update table1
SET column1 = t2.column1,
column1 = t2.column2,
...
FROM table1 INNER/LEFT/RIGHT JOIN table2 ON table1.column = table2.column
[WHERE conditions]

注:若不加where条件则是更新表中的所有数据,
故执行没有where子句的update要慎重再慎重。

实例:

update A
SET A1 = t2.B2 ,
A2 = t2.C1
FROM A INNER JOIN (
select B.B1,B.B2,C.C1
FROM B left join C on B.B3 = C.C3) t2
ON A.A3 = t2.B1
WHERE A.A4 = 1;