CODEWITHSUNDEEP
sqlsql-server
Immortal
Immortal

Reputation: 1180

How to Update table column in SQL SERVER DB based on condition

I have a table with the following information:

ID         Value_N1        Date_N1     Value_N4      Date_N4
1           NULL          2017-05-31    0.236        2017-02-28
2           NULL          2017-05-31    0.589        2017-02-28
3           NULL          2017-08-30    0.898        2017-08-30
4           NULL          2017-11-30    0.789        2017-11-30

I want to update the Value_N1 column with values from Value_N4 where Date_N1 is equal to Date_N4

I have tried to use the following query but am going nowhere with it:

Update TableName
set Value_N1 = (select Value_N4 from TbleName where Date_N1 = Date_N4)

Of-cause this query doesn't work because it returns more than one value. How can this be achieved?

Upvotes: 0

Views: 41

Answers (3)

Sandeep Tripathi
Sandeep Tripathi

Reputation: 59

update TableName set Value_N1=Value_N4 where Date_N1=Date_N4

Upvotes: 1

Ullas
Ullas

Reputation: 11556

You can also use a CASE expression.

Query

update TableName
set Value_N1 = (
    case when Date_N1 = Date_N4
    then Value_N4
    else Value_N1 end
);

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520888

Your logic for setting the new value to Value_N1 is on the right track, but the restrictions for which records to update should appear in a WHERE clause.

UPDATE TableName
SET Value_N1 = Value_N4
WHERE Date_N1 = Date_N4

Upvotes: 0

Related Questions