一、当用一个表中的数据来更新另一个表中的数据,T-SQL提供多种写法(下面列出了二种),但建议用第一种写法,虽然传统,但结构清晰。飞.飞Asp技术乐园
并且要注意,当用一个表中的数据来更新另一个表中的数据时,二个表一定要有关联!
1.
并且要注意,当用一个表中的数据来更新另一个表中的数据时,二个表一定要有关联!
1.
update t1 set t1.c2 = t2.c2
from t2
where t1.c1 = t2.c1
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 t1 inner join t2 on t1.c1 = t2.c1
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)
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)
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)
2.
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)
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 T_Sys_Area set Code=(b.code+'.' +CONVERT(varchar(10), T_Sys_Area.Sequence)) from (select * from T_Sys_Area) as b where T_Sys_Area.parentid=b.id