CODEWITHSUNDEEP
t-sql
Peter M
Peter M

Reputation: 7493

How can I make a complex T-SQL update query?

I have a database table:

Col1 | Col2 | Col3
------------------
  1  |   2  |  -
  2  |   3  |  -
  3  |   4  |  -
  4  |   5  |  -

Columns 1 and 2 have data but 3 is null.

What I want to achieve is to set Col3 to the Col1 value of the previous row (technically the previous row in which the Col1 value equals the Col2 value), to get this:

Col1 | Col2 | Col3
------------------
  1  |   2  |  -
  2  |   3  |  1
  3  |   4  |  2
  4  |   5  |  3

I am struggling over the update query in order to achieve this. I have been trying things like:

UPDATE Table
SET [cur].Col3 = [prev].Col1
FROM Table [cur], Table [prev]
WHERE [cur].Col1 = [prev].Col2

But this doesn't seem to be working for me. SQL Server accepts the syntax in a stored procedure, but when it executes it generates an error:

Table is ambiguous

What am I doing wrong?

Note:

The data in each column is already guaranteed to be unique and each combination of Col1 and Col2 is unique.

Upvotes: 4

Views: 4241

Answers (4)

KM.
KM.

Reputation: 103589

try:

declare @yourTable table (col1 int, col2 int, col3 int)
INSERT INTO @YourTable values (1,2,NULL)
INSERT INTO @YourTable values (2,3,NULL)
INSERT INTO @YourTable values (3,4,NULL)
INSERT INTO @YourTable values (4,5,NULL)

UPDATE cur
   SET cur.Col3 = prev.Col1
   FROM @YourTable           cur
       INNER JOIN @YourTable prev ON cur.col1=prev.col2

select * from @YourTable

output:

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(3 row(s) affected)
col1        col2        col3
----------- ----------- -----------
1           2           NULL
2           3           1
3           4           2
4           5           3

(4 row(s) affected)

Upvotes: 1

HLGEM
HLGEM

Reputation: 96552

UPDATE [cur] 
SET [cur].Col3 = [prev].Col1
FROM Table [cur]
JOIN Table [prev] on [cur].Col1 = [prev].Col2

Please try to start using the join syntax instead of that old style syntax. You will have fewer problems, it will be easier to maintain and no accidental cross-joins. ANd left joins will work correctly as they do not right now with the *= syntax. Plus you will be up-to-date with the 1992 standard.

Upvotes: 1

Anthony Faull
Anthony Faull

Reputation: 17957

Change the first line to

UPDATE [cur]

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1062600

UPDATE [cur] -- <<====== change here
SET [cur].Col3 = [prev].Col1
FROM Table [cur], Table [prev]
WHERE [cur].Col1 = [prev].Col2

Upvotes: 2

Related Questions