文章主要描述的是SQL Server UPDATE的赋值次序,假如你对SQL Server UPDATE的赋值次序实际操作感到十分头疼?如果是这样子的话,以下的文章将会给你相应的解决方案,以下就是相关内容的具体描述。
1) 先变量再字段
- SET NOCOUNT ON;
- DECLARE @i INT, @j INT
- DECLARE @Table TABLE
- (
- Id1 INT,
- Id2 INT
- );
- INSERT @Table(Id1, Id2)
- SELECT 1, 10
- UNION ALL
- SELECT 2, 20
- UNION ALL
- SELECT 3, 30;
- SELECT @i = 1, @j = 0
- UPDATE @Table SET Id1 = @i, Id2 = Id1, @i = @i + 1
- SELECT * FROM @Table
- SELECT @i = 1, @j = 0
- UPDATE @Table SET Id1 = @i, Id2 = @j, @j = @i + 10, @i = @i + 1
- SELECT * FROM @Table
- SET NOCOUNT OFF;
结果:
- Id1 Id2
- 2 1
- 3 2
- 4 3
- Id1 Id2
- 2 11
- 3 12
- 4 13
2) 变量之间, 从左到右
- SET NOCOUNT ON;
- DECLARE @i INT, @j INT
- DECLARE @Table TABLE
- (
- Id1 INT,
- Id2 INT
- );
- INSERT @Table(Id1, Id2)
- SELECT 1, 10;
- SELECT @i = 1, @j = 0
- UPDATE @Table SET @j = @i, @i = @i + 1
- PRINT '@i = ' + CAST(@i AS VARCHAR) + ', @j = ' + CAST(@j AS VARCHAR)
- SELECT @i = 1, @j = 0
- UPDATE @Table SET @i = @i + 1, @j = @i
- PRINT '@i = ' + CAST(@i AS VARCHAR) + ', @j = ' + CAST(@j AS VARCHAR)
- SELECT @i = 1, @j = 0
- UPDATE @Table SET @i = @j + 1, @j = @i
- PRINT '@i = ' + CAST(@i AS VARCHAR) + ', @j = ' + CAST(@j AS VARCHAR)
- SELECT @i = 1, @j = 0
- UPDATE @Table SET @i = @j, @j = @i
- PRINT '@i = ' + CAST(@i AS VARCHAR) + ', @j = ' + CAST(@j AS VARCHAR)
- SET NOCOUNT OFF;
结果:
- @i = 2, @j = 1
- @i = 2, @j = 2
- @i = 1, @j = 1
- @i = 0, @j = 0
3) 字段之间, 并行执行
- SET NOCOUNT ON;
- DECLARE @Table TABLE
- (
- Id1 INT, Id2 INT
- );
- INSERT @Table(Id1, Id2)
- SELECT 1, 10
- UNION ALL
- SELECT 2, 20
- UNION ALL
- SELECT 3, 30;
- UPDATE @Table SET Id1 = Id2, Id2 = Id1
- SELECT * FROM @Table
- SET NOCOUNT OFF;
结果:
- Id1 Id2
- 10 1
- 20 2
- 30 3
以上的相关内容就是对SQL Server UPDATE的赋值次序的介绍,望你能有所收获。
【编辑推荐】