我有一张几十万条记录的表,其中有company,address,companyid三个字段。
现在想将company,address相同内容记录的companyid更新成一个相同的。
以前companyid都不相同。不想用循环更新,因为那样速度太慢了,
哪位大哥能通过一条语句实现上述功能啊。主要是速度。小弟先谢谢了
现在想将company,address相同内容记录的companyid更新成一个相同的。
以前companyid都不相同。不想用循环更新,因为那样速度太慢了,
哪位大哥能通过一条语句实现上述功能啊。主要是速度。小弟先谢谢了
set companyid=(select min(companyid) from tablename where company=a.company and address=a.address)
from tablename a
select A.company,A.address,companyid from table A
right join
(select distinct company,address from table) B
on A.company=B.company and A.address=B.address
from
(
select * from 表名 a where not exists(select 1 from 表名 where company=a.company and address=a.address)
) c
where company=c.company and address=c.address
declare @tablename table
(
company int,
address int,
companyid int
)
insert into @tablename
select 1,2,11 union all
select 1,2,33 union all
select 1,3,22 union all
select 1,4,13 union all
select 1,2,34update a
set companyid=(select min(companyid) from @tablename where company=a.company and address=a.address)
from @tablename aselect * from @tablename/*
company address companyid
----------- ----------- -----------
1 2 11
1 2 11
1 3 22
1 4 13
1 2 11
*/
我才来csdn,怎么能把分数给你门啊
declare @tablename table
(
company int,
address int,
companyid int
)
insert into @tablename
select 1,2,11 union all
select 1,2,33 union all
select 1,3,22 union all
select 1,4,13 union all
select 1,2,34update a
set companyid=(select min(companyid) from @tablename where company=a.company and address=a.address)
from @tablename aselect * from @tablename/*
company address companyid
----------- ----------- -----------
1 2 11
1 2 11
1 3 22
1 4 13
1 2 11
*/LouisXIV(夜游神)
他们这样的解决办法真好~!~~让人更快的学到东西!~~谢谢!~~
真笨啊
还没做测试,不过俺还是用LouisXIV(夜游神) 的方法