如何进行复杂的T-SQL更新查询？

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.

第1列和第2列有数据,但3为空。

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:

我想要实现的是将Col3设置为前一行的Col1值(技术上是Col1值等于Col2值的前一行),以获得:

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:

但这似乎对我不起作用。 SQL Server接受存储过程中的语法,但是当它执行时会生成错误:

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.

每列中的数据已经保证是唯一的,并且Col1和Col2的每个组合都是唯一的。

4 个解决方案

#1

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)

#2

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

#3

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.

请尝试开始使用连接语法而不是旧式语法。您将遇到的问题更少,维护起来更容易,并且没有意外的交叉连接。左边连接将正常工作,因为它们现在没有使用* =语法。此外,您将获得1992年标准的最新信息。

#4

Change the first line to

将第一行更改为

UPDATE [cur]

#1