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.