supplegenius
supplegenius

Reputation: 1

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

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'

Upvotes: 0

Views: 182

Answers (1)

Ankit Bajpai
Ankit Bajpai

Reputation: 13527

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'

Upvotes: 1

Related Questions