--那就不用"where" 啊 Where 我是说,你要加的条件.. update sales set dealerid=a.dealerid from dealer a
那样不行,更新不了,它只会把dealer表的最后一个dealerid更新到sales
--比如这样 create table t1 (column1 varchar(10)) insert into t1 select 'a' insert into t1 select 'b' insert into t1 select 'c' insert into t1 select 'e'create table t2 (column2 varchar(10)) insert into t2 select 'd' insert into t2 select 'd' insert into t2 select 'd' insert into t2 select 'd'truncate table t2 insert into t2(column2) select column1 from t1 select * from t2--结果 column2 ---------- a b c e(所影响的行数为 4 行)--删除测试环境 Drop table T1,T2
是这样,sales表里,有销售信息,我不想清除,我只是想dealer表里的dealer替换掉所有的原来的dealer,至于替换顺序,规则怎么都行。而且我发现插入新数据好像行不通,我的代码写到这了,但是有问题,不知道怎么改 update dbo.IST_L_Monthlysales set dbo.IST_L_Monthlysales.dealerid = b.dealerid from (select top 1 dealerid from dbo.dealer where dealerid not in (select dealerid from dbo.IST_L_Monthlysales )) b
至于替换顺序,规则怎么都行--既然这样,何必写那么多(select top 1 dealerid from dbo.dealer where dealerid not in (select dealerid from dbo.IST_L_Monthlysales )) b 好麻烦哦直接 update dbo.IST_L_Monthlysales set dbo.IST_L_Monthlysales.dealerid = b.dealerid from dbo.IST_L_Monthlysales,b --这样更新就会按照物理顺序,更新IST_L_Monthlysales的dealerid等于b.dealerid
update dbo.IST_L_Monthlysales set dbo.IST_L_Monthlysales.dealerid = b.dealerid from dbo.dealerid b 不行,他只会更新dbo.IST_L_Monthlysales 的dealerid 为同一条数据
update dbo.IST_L_Monthlysales set dbo.IST_L_Monthlysales.dealerid = b.dealerid from dbo.IST_L_Monthlysales,dbo.dealer b 也不行,这个也会只把dealerid更新为deale表中最后一个dealerid
update sales set dealerid=a.dealerid from dealer a
create table t1 (column1 varchar(10))
insert into t1 select 'a'
insert into t1 select 'b'
insert into t1 select 'c'
insert into t1 select 'e'create table t2 (column2 varchar(10))
insert into t2 select 'd'
insert into t2 select 'd'
insert into t2 select 'd'
insert into t2 select 'd'truncate table t2
insert into t2(column2) select column1 from t1
select * from t2--结果
column2
----------
a
b
c
e(所影响的行数为 4 行)--删除测试环境
Drop table T1,T2
update dbo.IST_L_Monthlysales
set dbo.IST_L_Monthlysales.dealerid = b.dealerid
from (select top 1 dealerid from dbo.dealer where
dealerid not in (select dealerid from dbo.IST_L_Monthlysales )) b
dealerid not in (select dealerid from dbo.IST_L_Monthlysales )) b
好麻烦哦直接
update dbo.IST_L_Monthlysales
set dbo.IST_L_Monthlysales.dealerid = b.dealerid
from dbo.IST_L_Monthlysales,b
--这样更新就会按照物理顺序,更新IST_L_Monthlysales的dealerid等于b.dealerid
set dbo.IST_L_Monthlysales.dealerid = b.dealerid
from dbo.dealerid b
不行,他只会更新dbo.IST_L_Monthlysales 的dealerid 为同一条数据
set dbo.IST_L_Monthlysales.dealerid = b.dealerid
from dbo.IST_L_Monthlysales,dbo.dealer b
也不行,这个也会只把dealerid更新为deale表中最后一个dealerid