update t set id=(select top 1 name from tb where name = t.name id is not null) from tb
--name--》id add--》 and
--> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] create table [tb]([id] int,[name] int) insert [tb] select 1,2 union all select null,2 union all select 2,3 union all select null,3 union all select null,3update a set a.id=b.id from tb a,tb b where a.name=b.name and b.id is not null select * from [tb] /* id name ----------- ----------- 1 2 1 2 2 3 2 3 2 3(5 行受影响)*/
--创建临时表 IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL DROP TABLE #TEMP GO CREATE TABLE #TEMP ( ID INT, NAME CHAR(15) ) --测试数据 INSERT INTO #TEMP SELECT 1,'2' UNION ALL SELECT NULL,'2' UNION ALL SELECT 2,'3' UNION ALL SELECT NULL,'3'--查询结果 SELECT ID=(SELECT TOP 1 ID FROM #TEMP WHERE NAME=T.NAME ORDER BY ID DESC), NAME FROM #TEMP T
set id=(select top 1 name from tb where name = t.name id is not null)
from tb
--name--》id add--》 and
if object_id('[tb]') is not null drop table [tb]
create table [tb]([id] int,[name] int)
insert [tb]
select 1,2 union all
select null,2 union all
select 2,3 union all
select null,3 union all
select null,3update a set a.id=b.id from tb a,tb b where a.name=b.name and b.id is not null
select * from [tb]
/*
id name
----------- -----------
1 2
1 2
2 3
2 3
2 3(5 行受影响)*/
--创建临时表
IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL DROP TABLE #TEMP
GO
CREATE TABLE #TEMP
(
ID INT,
NAME CHAR(15)
)
--测试数据
INSERT INTO #TEMP
SELECT 1,'2'
UNION ALL
SELECT NULL,'2'
UNION ALL
SELECT 2,'3'
UNION ALL
SELECT NULL,'3'--查询结果
SELECT
ID=(SELECT TOP 1 ID FROM #TEMP WHERE NAME=T.NAME ORDER BY ID DESC),
NAME
FROM #TEMP T