根据另一列更新列。

I've a table with four columns and want to update col4(if its col3 value is false) according to col1 of that row which has true value of col3 and its col2 is equal to updated col2. Data is like

我有一个包含四列的表，我想根据该行的col1更新col4(如果它的col3值为false)， col3的真实值为col3，它的col2等于更新后的col2。数据就像

Col1 Col2 Col3 Col4

1     JOhn  false   NULL
2     Hony  false   NULL
3     John   true   NULL
4     Rohn  false   NULL
5     Hony  true   NULL

I want that col4 of 1st row would have 3 and col4 of 2nd row would have 5 in it.

我想要第一行的col4是3，而第2行的col4是5。

My query is

我的查询

Update tab
set tab.col4 = tab.col1
from table tab
where tab.col3 = true

But it only updates row that has true value.

但它只更新具有真正值的行。

2 个解决方案

#1

Just tried it with SQL Fiddle:

只是用SQL Fiddle:

create table tbl_SO_19
(
col1 int,
col2 varchar(50),
col3 bit,
col4 int
)
go
insert into tbl_SO_19
values
(1,'John',0,null),
(2,'Hony',0,null),
(3,'John',1,null),
(4,'Rohn',0,null),
(5,'Hony',1,null)

now you can use below query to update it like as you wanted:

现在您可以使用下面的查询来更新它，就像您想要的那样:

Update tbl_SO_19
set col4 = t.col1
from tbl_SO_19 join tbl_SO_19 t on t.col2=tbl_SO_19.col2 and t.col3=1
where tbl_SO_19.col3 = 0

#2

You need to use a self join:

你需要使用自连接:

UPDATE a
SET    a.col4 = b.col1
FROM   mytable b
JOIN   (SELECT col1, col2
        FROM   mytable
        WHERE  col3 = true) b ON a.col2 = b.col2
WHERE  col3 = false

#1