CODEWITHSUNDEEP
sql-serverdatabase
Naila Akbar
Naila Akbar

Reputation: 3358

Update column according to another column

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 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.

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.

Upvotes: 1

Views: 100

Answers (2)

void
void

Reputation: 7890

Just tried it with 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

Upvotes: 1

Mureinik
Mureinik

Reputation: 312136

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

Upvotes: 1

Related Questions