34,575
社区成员
发帖
与我相关
我的任务
分享
insert tb
select ts,tt,td,ty from tb where sn=@sn
DECLARE @TB TABLE([sn] INT, [ts] VARCHAR(2), [tt] VARCHAR(2), [td] INT, [ty] INT)
INSERT @TB
SELECT 1, '02', '08', 11, 22 UNION ALL
SELECT 2, '05', '08', 09, 26 UNION ALL
SELECT 3, NULL, NULL, NULL, NULL UNION ALL
SELECT 4, NULL, NULL, NULL, NULL
DECLARE @SN INT,@MSN INT
SET @SN=1
SET @MSN=(SELECT min(sn) FROM @TB WHERE ts is null and tt is null and td is null and ty is null)
UPDATE @TB set ts=t.ts,tt=t.tt,td=t.td,ty=t.ty
FROM @TB AS A ,(SELECT ts,tt,td,ty FROM @TB WHERE sn=@SN) t
WHERE A.sn=@MSN
SELECT * FROM @TB
/*
sn ts tt td ty
----------- ---- ---- ----------- -----------
1 02 08 11 22
2 05 08 9 26
3 02 08 11 22
4 NULL NULL NULL NULL
*/
DECLARE @TB TABLE([sn] INT, [ts] VARCHAR(2), [tt] VARCHAR(2), [td] INT, [ty] INT)
INSERT @TB
SELECT 1, '02', '08', 11, 22 UNION ALL
SELECT 2, '05', '08', 09, 26 UNION ALL
SELECT 3, NULL, NULL, NULL, NULL UNION ALL
SELECT 4, NULL, NULL, NULL, NULL
DECLARE @SN INT,@MSN INT
SET @SN=1
SET @MSN=3
UPDATE @TB set ts=t.ts,tt=t.tt,td=t.td,ty=t.ty
FROM @TB AS A ,(SELECT ts,tt,td,ty FROM @TB WHERE sn=@SN) t
WHERE A.sn=3
SELECT * FROM @TB
/*
sn ts tt td ty
----------- ---- ---- ----------- -----------
1 02 08 11 22
2 05 08 9 26
3 02 08 11 22
4 NULL NULL NULL NULL
*/