修改重复数据、条件：名称、电话重复，并且地址保留最长的那条记录

[tashiwoweiyi]

--假设有一个表，ATable,
--字段：id,name,address,phone,isdel
--假删除重复数据,也就是修改字段【isdel】标识(isdel=1 删除)，
--其中条件：名称、电话重复，并且地址保留最长的那条记录
--
--如：
--id		name		address			phone		isdel
--1		a		三元里大道497号		0205524257	0
--2		a		三元里大道497		0205524257	0
--3		b		体育东路497号		0207854125        0
--4		b		体育东路497		0207854125        0
--5		b		体育东路			0207854125        0
--6		c		黄埔大道188号		0204569875	0
--
--想要的结果
--id等于2、4、5的isdel修改成1

[xuam]

update ATable
set isdel =1 
where not exists  (select 1 from ATable having max(address) group by id)
  

[-晴天]

create table ATable(id int,name varchar(10),address nvarchar(20),phone varchar(15),isdel int)
--假删除重复数据,也就是修改字段【isdel】标识(isdel=1 删除)，
--其中条件：名称、电话重复，并且地址保留最长的那条记录
insert into ATable select 1,'a','三元里大道497号','0205524257',0
insert into ATable select 2,'a','三元里大道497','0205524257',0
insert into ATable select 3,'b','体育东路497号','0207854125',    0
insert into ATable select 4,'b','体育东路497','0207854125',    0
insert into ATable select 5,'b','体育东路','0207854125',    0
insert into ATable select 6,'c','黄埔大道188号','0204569875',0
go
update a set isdel=1 from atable a 
where exists(select 1 from atable where name=a.name and phone=a.phone and len(address)>len(a.address))
go
select * from ATable
/*
id          name       address              phone           isdel
----------- ---------- -------------------- --------------- -----------
1           a          三元里大道497号            0205524257      0
2           a          三元里大道497             0205524257      1
3           b          体育东路497号             0207854125      0
4           b          体育东路497              0207854125      1
5           b          体育东路                 0207854125      1
6           c          黄埔大道188号             0204569875      0

(6 行受影响)

*/
drop table atable

[Mr_Nice]

if object_id('[ATable]') is not null drop table [ATable]
go
create table [ATable] (id int,name nvarchar(2),address nvarchar(18),phone nvarchar(20),isdel int)
insert into [ATable]
select 1,'a','三元里大道497号','0205524257',0 union all
select 2,'a','三元里大道497','0205524257',0 union all
select 3,'b','体育东路497号','0207854125',0 union all
select 4,'b','体育东路497','0207854125',0 union all
select 5,'b','体育东路','0207854125',0 union all
select 6,'c','黄埔大道188号','0204569875',0

select * from [ATable]

SELECT id,NAME,ADDRESS,phone,CASE WHEN id IN(2,4,5) then 1 else isdel END AS isdel FROM Atable 


/*
id          NAME ADDRESS            phone                isdel
----------- ---- ------------------ -------------------- -----------
1           a    三元里大道497号          0205524257           0
2           a    三元里大道497           0205524257           1
3           b    体育东路497号           0207854125           0
4           b    体育东路497            0207854125           1
5           b    体育东路               0207854125           1
6           c    黄埔大道188号           0204569875           0

(6 行受影响)
*/

[bancxc]

sfsf

[xiangyehpu]

update aTable
set isdel = 1
from aTable a,(select id,row_number() over(partition by name,phone order by len(address) desc) as row from ATable) b
where a.id = b.id
and b.row = 1

[GoAwayZ]

update a set isdel=1 from tb a 
where exists(select 1 from tb 
              where name=a.name and phone=a.phone and len(address)>len(a.address))

[唐诗三百首]

update a set isdel=1 
from atable a 
inner join atable b 
on a.name=b.name and a.phone=b.phone and len(a.address)<len(b.address)

另:楼主在广州的吧,我也在广州.

[--小F--]

update
  a 
set
 isdel=1 
from
 atable a 
where
 exists(select 1 from atable where name=a.name and phone=a.phone and len(address)>len(a.address))