多张表UPDATE用法

╰+哭是因爲堅強的太久メ 2023-06-27 12:59 15阅读 0赞

sql 语句多张表UPDATE用法  
一、当用一个表中的数据来更新另一个表中的数据，T-SQL提供多种写法(下面列出了二种)，但建议用第一种写法，虽然传统，但结构清晰。  
并且要注意，当用一个表中的数据来更新另一个表中的数据时，二个表一定要有关联！  
1.  
update t1 set t1.c2 = t2.c2  
from t2  
where t1.c1 = t2.c1  
2.  
Update t1 set t1.c2 = t2.c2  
from t1 inner join t2 on t1.c1 = t2.c1  
二、FROM 子句中指定的表的别名不能作为 SET column\_name 子句中被修改字段的限定符使用。  
UPDATE titles  
SET t.ytd\_sales = t.ytd\_sales + s.qty  
FROM titles t, sales s  
WHERE t.title\_id = s.title\_id  
AND s.ord\_date = (SELECT MAX(sales.ord\_date) FROM sales)

若要使上例合法，请从列名中删除别名 t 或使用本身的表名。  
1.  
UPDATE titles  
SET ytd\_sales = t.ytd\_sales + s.qty  
FROM titles t, sales s  
WHERE t.title\_id = s.title\_id  
AND s.ord\_date = (SELECT MAX(sales.ord\_date) FROM sales)

UPDATE titles  
SET titles.ytd\_sales = t.ytd\_sales + s.qty  
FROM titles t, sales s  
WHERE t.title\_id = s.title\_id  
AND s.ord\_date = (SELECT MAX(sales.ord\_date) FROM sales)

EXAMPLE  
update item\_master i set i.contral\_ma= ( select max(b.control\_ma) from base\_complex b where b.code=i.hs\_code );  
更新一列：  
update mytab a set name=(select b.name from goal b where b.id=a.id)  
where exists (select 1 from goal b where b.id=a.id);  
更新多列：  
update mytab a  
set (name,address)=(select b.name,b.address  
from goal b  
where b.id=a.id)  
where exists (select 1  
from goal b  
where b.id=a.id )  
特别是要注意exists后面的语句：）这会让目标行不至于为NULL

更新update多个关联表的SQL写法：  
update customers a  
set city\_name=(select b.city\_name from tmp\_cust\_city b where b.customer\_id=a.customer\_id)  
where exists (select 1  
from tmp\_cust\_city b  
where b.customer\_id=a.customer\_id  
)  
update 超过2个值  
update customers a  
set (city\_name，customer\_type)=(select b.city\_name，b.customer\_type  
from tmp\_cust\_city b  
where b.customer\_id=a.customer\_id)  
where exists (select 1  
from tmp\_cust\_city b  
where b.customer\_id=a.customer\_id  
)