一个简单SQL的UPDATE问题

[gt_sql]

with  a  as 

   （select distinct charge_code,supplyer
			from yp_in_detl 
				where supplyer is not null 
					and supplyer <>'111111'
					and supplyer <>''
					and charge_code not exists
('015217','014747','015325','015360',
'015318','015215','015178','015098','015215','015178',
'015199','015106','015092','015082','015004','015028','015049','015018',
'014996','014926','014888','014859','014877','014846','014840','014834',
'014836','014827','014829','014830','014821','014813','014800','014776',
'014797','014758','014744','014731','014681','014672','014650','007084',
'014643','014631','014634','013828','013477','013620','014409','013064',
'007605','007103','007551','007382','005839','007068','014654','014746'))

update yp_dict  set trad_mark=a.supplyer where charge_code=a.charge_code

错误提示：无法绑定由多个部分组成的标识符 "a.charge_code"。

改成下面的语句可以吗？
with  a  as 

   （select distinct charge_code,supplyer
			from yp_in_detl 
				where supplyer is not null 
					and supplyer <>'111111'
					and supplyer <>''
					and charge_code not exists
('015217','014747','015325','015360',
'015318','015215','015178','015098','015215','015178',
'015199','015106','015092','015082','015004','015028','015049','015018',
'014996','014926','014888','014859','014877','014846','014840','014834',
'014836','014827','014829','014830','014821','014813','014800','014776',
'014797','014758','014744','014731','014681','014672','014650','007084',
'014643','014631','014634','013828','013477','013620','014409','013064',
'007605','007103','007551','007382','005839','007068','014654','014746'))

update yp_dict  set trad_mark=(select supplyer from  a) where charge_code=(select  charge_code from  a)

另外，这个语句可以怎么优化修改下
 

[--小F--]

update b  set trad_mark=a.supplyer from yp_dict b,a where charge_code=a.charge_code

[gt_sql]

[Quote=引用 1 楼 的回复:]
SQL code
update b set trad_mark=a.supplyer from yp_dict b,a where charge_code=a.charge_code
[/Quote]

犯了一个这个低级的错误

还能不能把with a as 部分查询优化下

[开着拖拉机泡妞]

update update b  set trad_mark=a.supplyer inner join yp_dict b on b.charge_code=a.charge_code

[开着拖拉机泡妞]

with  a  as 

   （select distinct charge_code,supplyer
            from yp_in_detl 
                where supplyer is not null 
                    and supplyer <>'111111'
                    and supplyer <>''
                    and charge_code not in
('015217','014747','015325','015360',
'015318','015215','015178','015098','015215','015178',
'015199','015106','015092','015082','015004','015028','015049','015018',
'014996','014926','014888','014859','014877','014846','014840','014834',
'014836','014827','014829','014830','014821','014813','014800','014776',
'014797','014758','014744','014731','014681','014672','014650','007084',
'014643','014631','014634','013828','013477','013620','014409','013064',
'007605','007103','007551','007382','005839','007068','014654','014746'))

[gt_sql]

[Quote=引用 3 楼 的回复:]
SQL code

update update b set trad_mark=a.supplyer inner join yp_dict b on b.charge_code=a.charge_code
[/Quote]
还能不能把with a as 部分查询优化下

[开着拖拉机泡妞]

with  a  as 

   （select distinct charge_code,supplyer
            from yp_in_detl 
                where supplyer is not null 
                    and supplyer <>'111111'
                    and supplyer <>''
                    and charge_code not in
('015217','014747','015325','015360',
'015318','015215','015178','015098','015215','015178',
'015199','015106','015092','015082','015004','015028','015049','015018',
'014996','014926','014888','014859','014877','014846','014840','014834',
'014836','014827','014829','014830','014821','014813','014800','014776',
'014797','014758','014744','014731','014681','014672','014650','007084',
'014643','014631','014634','013828','013477','013620','014409','013064',
'007605','007103','007551','007382','005839','007068','014654','014746'))


--这样应该可以。像油画建议你提供原始数据和期待结果

[gt_sql]

x谢谢