求一个sql

[lawbc]

记录如下：
ID NUM
1 11
2 11
3 3
4 6
5 7


比如当前ID为4，对应NUM为6，怎么样更新当前id为4的记录的NUM的值，NUM的值计算规则为，取比他本身大的记录中的最小的一个，如果没有的话，那么就为本身，这里就是7了

[lxq19851204]

Update T1 A join (Select max(NUM) as NUM From t1)B
set A.sNUM=B.NUM where A.ID=4

[lawbc]

LS的好像不对吧，你join的是最大值，而我要求的是比当前查询的NUM的值大的记录中的最小的一个

[飘零一叶]

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB
( 
   ID  INT,
   NUM INT
)
INSERT INTO TB
SELECT 1,11 UNION ALL
SELECT 2,11 UNION ALL
SELECT 3,3  UNION ALL
SELECT 4,6  UNION ALL
SELECT 5,7

UPDATE TB 
SET NUM=CASE WHEN EXISTS(SELECT 1 FROM TB T1 WHERE T1.ID>TB.ID) THEN (SELECT MIN(NUM) FROM TB T1 WHERE T1.ID>TB.ID ) ELSE TB.NUM END

SELECT * FROM TB
-------------------------
ID	NUM
1	3
2	3
3	6
4	7
5	7

[abuying]

update tb 
set num=(select min(num) from tb where num>a.num) from tb a
 where  exists( select 1 from tb b where b.num>a.num)

[jaydom]

IF object_id('tb') IS NOT NULL
DROP TABLE tb
go
CREATE TABLE tb (id int,num int)
INSERT INTO tb
SELECT 1, 11 UNION all
SELECT 2, 11 UNION all
SELECT 3, 3 UNION all
SELECT 4, 6 UNION all
SELECT 5, 7

UPDATE tb 
SET num=CASE WHEN (SELECT min(num) FROM tb  WHERE num>a.num ) IS NOT NULL 
             THEN (SELECT min(num) FROM (SELECT num FROM tb WHERE num>a.num) b)
        ELSE num END
FROM tb a
WHERE id=4

SELECT * FROM tb

1	11
2	11
3	3
4	7
5	7

[fpzgm]

#3还是不对，还是我来吧

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB
( 
   ID  INT,
   NUM INT
)
INSERT INTO TB
SELECT 1,11 UNION ALL
SELECT 2,11 UNION ALL
SELECT 3,3  UNION ALL
SELECT 4,6  UNION ALL
SELECT 5,7  UNION ALL
SELECT 6,5

UPDATE TB 
SET NUM=CASE WHEN EXISTS(SELECT 1 FROM TB T1 WHERE T1.ID>TB.ID) 
        THEN (SELECT top 1 NUM FROM TB T1 WHERE T1.ID>TB.ID order by ID) 
        ELSE TB.NUM END
WHERE ID=4

SELECT * FROM TB

/*
ID	NUM
1	11
2	11
3	3
4	7
5	7
6	5
*/

[nice_dream_li]

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB
( 
   ID  INT,
   NUM INT
)
INSERT INTO TB
SELECT 1,11 UNION ALL
SELECT 2,11 UNION ALL
SELECT 3,3  UNION ALL
SELECT 4,6  UNION ALL
SELECT 5,7

ID    NUM
1    3
2    3
3    6
4    7
5    7
update tb 
set num=(select isnull(min(num),a.num) from tb where id>a.id) from tb a
select * from tb

[birdmonster]

UPDATE TB SET NUM=
(
SELECT TOP 1 NUM FROM TB A WHERE A.NUM>E.NUM ORDER BY A.NUM
) FROM TB E
WHERE ID=4 AND EXISTS(SELECT TOP 1 NUM FROM TB A WHERE A.NUM>E.NUM ORDER BY A.NUM)