SQL UPDATE语句根据另一个现有行更新列 [英] SQL UPDATE statement to update a column based on another existing row

问题描述

基本上，我有一个表格，其格式与下表类似.

Basically I have a table that has similar format to the below table.

我要做的是基于此逻辑更新Col4

What I want to do is update Col4 based on this logic

• 如果Col2为空，则使用Col3更新Col4
• 如果Col2不为null，则在Col1中找到与Col2中的值匹配的值.用col3中的相应值更新col4

例如，给出此表:

| Col1 | Col2 | Col3 | Col4 |
-----------------------------
|  1   |   2  |  A1  |  2   |
-----------------------------
|  2   |   3  |  A2  |  3   |
-----------------------------
|  3   |{null}|  A3  |{null}|

将其更新为该表

| Col1 | Col2 | Col3 | Col4 |
-----------------------------
|  1   |   2  |  A1  |  A2  |
-----------------------------
|  2   |   3  |  A2  |  A3  |
-----------------------------
|  3   |{null}|  A3  |  A3  |

任何方向将不胜感激！

推荐答案

类似的事情应该起作用(未经测试):

Something like this should work (untested):

UPDATE  table
SET     col4 = CASE WHEN table.col2 IS NULL THEN table.col3 ELSE col2Matches.col3 END
FROM    table
        INNER JOIN table AS col2Matches
            ON  table.col2 = col2Matches.col1

这应该让您对其进行测试:

this should let you test it:

SELECT  CASE WHEN table.col2 IS NULL THEN table.col3 ELSE col2Matches.col3 END
FROM    table
        INNER JOIN table AS col2Matches
            ON  table.col2 = col2Matches.col1

希望这会有所帮助，

Pete

这篇关于SQL UPDATE语句根据另一个现有行更新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！