Update column values based on values in same column (different row) MSSQL

Question

I'm having trouble changing a column value based on a value from the same column.

If the "setting_id" of D244 has a "setting_value" of 1, then I need to change " 8F60 to 1 as well.

Here is a sample datatable settings

| setting_id | setting_value |
| ---------- | ------------- |
|   D244     |       1       |
|   8F60     |       0       |
|   AD4F     |       1       |

settings

My attempt:

UPDATE settings s, (SELECT DISTINCT setting_id, setting_value FROM settings WHERE setting_value like '1' and setting_id like 'D244') s1
SET s.settingValue = s1.settingValue
WHERE s.settingID like '8F60'

Ankit Bajpai · Accepted Answer · 2017-09-07 19:03:39Z

1

Try this -

UPDATE settings s INNER JOIN settings s1
ON s1.setting_id = s.setting_id
SET s.settingValue = s1.settingValue
WHERE s.settingID like '8F60'
AND s1.setting_id = 'D244'

answered Sep 7, 2017 at 19:03

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update column values based on values in same column (different row) MSSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related