Assignment order for SQL SERVER update

Source: Internet
Author: User

1) First variable and then field

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 + ten, @i = @i + 1
SELECT * from @Table

SET NOCOUNT off;
Results:
Id1 ID2
----------- -----------
2 1
3 2
4 3

Id1 ID2
----------- -----------
2 11
3 12
4 13


2 between variables, from left to right

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;
Results:
@i = 2, @j = 1
@i = 2, @j = 2
@i = 1, @j = 1
@i = 0, @j = 0


3) Between fields, parallel execution

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;
Results:
Id1 ID2
----------- -----------
10 1
20 2
30 3

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.