B.把 WHERE 子句和 UPDATE 语句一起使用 WHERE 子句指定要更新的行例如,在下面这个虚构的事件中,北加利福尼亚更名为 Pacifica(缩写为 PC),而奥克兰的市民投票决定将其城市的名字改为 Bay City。这个例子说明如何为奥克兰市以前的所有居民(他们的地址已经过时)更新表 authors。UPDATE authors SET state = 'PC', city = 'Bay City' WHERE state = 'CA' AND city = 'Oakland'必须编写另一个语句来更改北加利福尼亚其它城市的居民所在的州名。
update a set col1=a.col1,col2=a.col2,col3=b.col3,col4=b.col4 from tablea a ,tableb b where a.id = b.id and a.id = 'xxx'我也比较弱,以前没有写过看帮助才改的。 你自己看看,要是错误了再问问高手。
update s_ord y set (ord_wgt,deliv_date_to,deliv_date_from,ord_amount)= (select sum(a.ord_wgt) ord_wgt,max(a.deliv_date_to) deliv_date_to,min(a.deliv_date_from) deliv_date_from, sum(((a.base_price + a.extra - a.dc)-(ISNULL(b.dc_trans,0)+ISNULL(b.dc_lot,0)+ISNULL(b.dc_group,0)+ ISNULL(b.dc_bef_mon,0)+ISNULL(b.dc_case_1,0)+ISNULL(b.dc_case_2,0)+ISNULL(b.dc_case_3,0)+ISNULL(b.dc_etc,0))) * (a.ord_wgt / 1000))amt from (select contract_no,contract_no_seq,sum(base_price) base_price,sum(ISNULL(ord_wgt,0)) ord_wgt, sum(ISNULL(dc_coat_type,0)+ISNULL(dc_wide_width,0)+ISNULL(dc_color_cruse,0)+ISNULL(dc_etc,0)+ ISNULL(dc_contract,0)+ISNULL(dc_case_1,0)+ISNULL(dc_case_2,0)+ISNULL(dc_case_3,0)) dc, sum(ISNULL(extra_raw,0)+ISNULL(extra_pok,0)+ISNULL(extra_coat,0)+ISNULL(extra_two_col,0)+ISNULL(extra_protect,0)+ ISNULL(extra_etc,0)+ISNULL(extra_case_3,0)+ISNULL(extra_case_2,0)+ISNULL(extra_case_3,0)) extra, max(deliv_date_to) deliv_date_to,min(deliv_date_from) deliv_date_from From s_ord_det where contract_no = 'JSR070000' and ISNULL(ord_end_gb,'A') not in ('C','U') group by contract_no,contract_no_seq) a, s_ord b Where a.Contract_NO = b.Contract_NO and ISNULL(b.ord_end_gb,'A') not in ('C','U') group by a.contract_no) where y.contract_no = 'JSR070000' 其实原来的是这个语句,我就是不会呀,痛苦啊
先用select查询出来,然后变换为update,含where条件就可以啦
update table a set col1 = b.col1, col2 = b.col2, col3 = b.col3, col4 = b.col4 from a , b where a.id = 'xxx' and a.id = b.id
update s_ord set ord_wgt =b.ord_wgt ,deliv_date_to =b.deliv_date_to ,deliv_date_from=b.deliv_date_from ,ord_amount =b.ord_amount from s_ord a join (select a.contract_no,sum(a.ord_wgt) ord_wgt,max(a.deliv_date_to) deliv_date_to,min(a.deliv_date_from) deliv_date_from, sum(((a.base_price + a.extra - a.dc)-(ISNULL(b.dc_trans,0)+ISNULL(b.dc_lot,0)+ISNULL(b.dc_group,0)+ ISNULL(b.dc_bef_mon,0)+ISNULL(b.dc_case_1,0)+ISNULL(b.dc_case_2,0)+ISNULL(b.dc_case_3,0)+ISNULL(b.dc_etc,0))) * (a.ord_wgt / 1000)) ord_amount from (select contract_no,contract_no_seq,sum(base_price) base_price,sum(ISNULL(ord_wgt,0)) ord_wgt, sum(ISNULL(dc_coat_type,0)+ISNULL(dc_wide_width,0)+ISNULL(dc_color_cruse,0)+ISNULL(dc_etc,0)+ ISNULL(dc_contract,0)+ISNULL(dc_case_1,0)+ISNULL(dc_case_2,0)+ISNULL(dc_case_3,0)) dc, sum(ISNULL(extra_raw,0)+ISNULL(extra_pok,0)+ISNULL(extra_coat,0)+ISNULL(extra_two_col,0)+ISNULL(extra_protect,0)+ ISNULL(extra_etc,0)+ISNULL(extra_case_3,0)+ISNULL(extra_case_2,0)+ISNULL(extra_case_3,0)) extra, max(deliv_date_to) deliv_date_to,min(deliv_date_from) deliv_date_from From s_ord_det where contract_no = 'JSR070000' and ISNULL(ord_end_gb,'A') not in ('C','U') group by contract_no,contract_no_seq) a, s_ord b Where a.Contract_NO = b.Contract_NO and ISNULL(b.ord_end_gb,'A') not in ('C','U') group by a.contract_no) b on a.contract_no=b.contract_no where a.contract_no = 'JSR070000' 哦,修改好了,哈哈。。
WHERE 子句指定要更新的行例如,在下面这个虚构的事件中,北加利福尼亚更名为 Pacifica(缩写为 PC),而奥克兰的市民投票决定将其城市的名字改为 Bay City。这个例子说明如何为奥克兰市以前的所有居民(他们的地址已经过时)更新表 authors。UPDATE authors
SET state = 'PC', city = 'Bay City'
WHERE state = 'CA' AND city = 'Oakland'必须编写另一个语句来更改北加利福尼亚其它城市的居民所在的州名。
set col1=a.col1,col2=a.col2,col3=b.col3,col4=b.col4
from tablea a ,tableb b
where a.id = b.id and a.id = 'xxx'我也比较弱,以前没有写过看帮助才改的。
你自己看看,要是错误了再问问高手。
(select sum(a.ord_wgt) ord_wgt,max(a.deliv_date_to) deliv_date_to,min(a.deliv_date_from) deliv_date_from,
sum(((a.base_price + a.extra - a.dc)-(ISNULL(b.dc_trans,0)+ISNULL(b.dc_lot,0)+ISNULL(b.dc_group,0)+
ISNULL(b.dc_bef_mon,0)+ISNULL(b.dc_case_1,0)+ISNULL(b.dc_case_2,0)+ISNULL(b.dc_case_3,0)+ISNULL(b.dc_etc,0)))
* (a.ord_wgt / 1000))amt from (select contract_no,contract_no_seq,sum(base_price) base_price,sum(ISNULL(ord_wgt,0)) ord_wgt,
sum(ISNULL(dc_coat_type,0)+ISNULL(dc_wide_width,0)+ISNULL(dc_color_cruse,0)+ISNULL(dc_etc,0)+
ISNULL(dc_contract,0)+ISNULL(dc_case_1,0)+ISNULL(dc_case_2,0)+ISNULL(dc_case_3,0)) dc,
sum(ISNULL(extra_raw,0)+ISNULL(extra_pok,0)+ISNULL(extra_coat,0)+ISNULL(extra_two_col,0)+ISNULL(extra_protect,0)+
ISNULL(extra_etc,0)+ISNULL(extra_case_3,0)+ISNULL(extra_case_2,0)+ISNULL(extra_case_3,0)) extra,
max(deliv_date_to) deliv_date_to,min(deliv_date_from) deliv_date_from
From s_ord_det
where contract_no = 'JSR070000'
and ISNULL(ord_end_gb,'A') not in ('C','U')
group by contract_no,contract_no_seq) a, s_ord b
Where a.Contract_NO = b.Contract_NO
and ISNULL(b.ord_end_gb,'A') not in ('C','U')
group by a.contract_no)
where y.contract_no = 'JSR070000'
其实原来的是这个语句,我就是不会呀,痛苦啊
set col1 = b.col1,
col2 = b.col2,
col3 = b.col3,
col4 = b.col4
from a , b
where a.id = 'xxx' and a.id = b.id
ord_wgt =b.ord_wgt
,deliv_date_to =b.deliv_date_to
,deliv_date_from=b.deliv_date_from
,ord_amount =b.ord_amount
from s_ord a join
(select a.contract_no,sum(a.ord_wgt) ord_wgt,max(a.deliv_date_to) deliv_date_to,min(a.deliv_date_from) deliv_date_from,
sum(((a.base_price + a.extra - a.dc)-(ISNULL(b.dc_trans,0)+ISNULL(b.dc_lot,0)+ISNULL(b.dc_group,0)+
ISNULL(b.dc_bef_mon,0)+ISNULL(b.dc_case_1,0)+ISNULL(b.dc_case_2,0)+ISNULL(b.dc_case_3,0)+ISNULL(b.dc_etc,0)))
* (a.ord_wgt / 1000)) ord_amount from (select contract_no,contract_no_seq,sum(base_price) base_price,sum(ISNULL(ord_wgt,0)) ord_wgt,
sum(ISNULL(dc_coat_type,0)+ISNULL(dc_wide_width,0)+ISNULL(dc_color_cruse,0)+ISNULL(dc_etc,0)+
ISNULL(dc_contract,0)+ISNULL(dc_case_1,0)+ISNULL(dc_case_2,0)+ISNULL(dc_case_3,0)) dc,
sum(ISNULL(extra_raw,0)+ISNULL(extra_pok,0)+ISNULL(extra_coat,0)+ISNULL(extra_two_col,0)+ISNULL(extra_protect,0)+
ISNULL(extra_etc,0)+ISNULL(extra_case_3,0)+ISNULL(extra_case_2,0)+ISNULL(extra_case_3,0)) extra,
max(deliv_date_to) deliv_date_to,min(deliv_date_from) deliv_date_from
From s_ord_det
where contract_no = 'JSR070000'
and ISNULL(ord_end_gb,'A') not in ('C','U')
group by contract_no,contract_no_seq) a, s_ord b
Where a.Contract_NO = b.Contract_NO
and ISNULL(b.ord_end_gb,'A') not in ('C','U')
group by a.contract_no) b
on a.contract_no=b.contract_no
where a.contract_no = 'JSR070000'
哦,修改好了,哈哈。。