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

Question

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 Col3 value of the previous row (technically the previous row in which the Col1 value is equal to the Col2 value) to get the following:

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

I am trying to fulfill an update request to achieve this. I tried things like:

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

But that doesn't seem to work for me. SQL Server accepts the syntax in the stored procedure, but generates an error when it is executed:

The table is ambiguous

What am I doing wrong?

---

Note:

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

Answer 1

to 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) 

Answer 2

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

Answer 3

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

Try to start using join syntax instead of old-style syntax. You will have fewer problems, it will be easier to maintain and there will be no random cross-connects. ANd left joins will work correctly because they do not work directly with * = syntax. In addition, you will be aware of the 1992 standard.

Answer 4

Change the first line to

 UPDATE [cur]