有个tb表id name code
1 a1 001
2 a2 002
3 b1 003
4 c1 004现在想把id为3 的code 升一级(变为002)则原本code 是002的就要降一级(变为003)
sql怎么写呀
最后实现的表
id name code
1 a1 001
2 a2 003
3 b1 002
4 c1 004
1 a1 001
2 a2 002
3 b1 003
4 c1 004现在想把id为3 的code 升一级(变为002)则原本code 是002的就要降一级(变为003)
sql怎么写呀
最后实现的表
id name code
1 a1 001
2 a2 003
3 b1 002
4 c1 004
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (id INT,name VARCHAR(2),code VARCHAR(3))
INSERT INTO @tb
SELECT 1,'a1','001' UNION ALL
SELECT 2,'a2','002' UNION ALL
SELECT 3,'b1','003' UNION ALL
SELECT 4,'c1','004'--SQL查询如下:DECLARE @id int;
SET @id=3;DECLARE @up_id int;
SET @up_id=(SELECT TOP 1 id FROM @tb WHERE id<@id ORDER BY id DESC);IF @up_id IS NULL
RAISERROR('Can not''t Update!',16,1);
ELSE
UPDATE A SET
code=B.code
FROM @tb AS A
JOIN @tb AS B
ON A.id BETWEEN @up_id AND @id
AND B.id BETWEEN @up_id AND @id
WHERE A.id=CASE WHEN B.id=@up_id THEN @id ELSE @up_id END
--查看结果
SELECT * FROM @tb/*
id name code
----------- ---- ----
1 a1 001
2 a2 003
3 b1 002
4 c1 004(4 行受影响)*/