CODEWITHSUNDEEP
sql
Awn85
Awn85

Reputation: 1

Update rows within the same table

I am trying to update rows within a table based off of the data within the same table. Below is the query that I have written...

Update qf.UserCustomProperty

SET PropertyValue = '1'

SELECT      
[UserId]
FROM qf.UserCustomProperty
WHERE PropertyId = 49
and UserId IN
(SELECT
 UserId
 FROM qf.UserCustomProperty
 WHERE PropertyId = 47
 AND PropertyValue = 1)

I am trying to update the users that have propertyid of 49 were the same user has a propertyid of 47 and a propertyvalue of 1. The script is updating all users with a propertyid of 47 to have a propertyvalue of 1.

Upvotes: 0

Views: 61

Answers (2)

Rahul Verma
Rahul Verma

Reputation: 3089

I see you've written the logic correctly but you've actually wrote it in two separate queries. I've just joined your two separate queries into one. This should work.

Update qf.UserCustomProperty
SET PropertyValue = '1'
WHERE PropertyId = 49
and UserId IN
(SELECT
 UserId
 FROM qf.UserCustomProperty
 WHERE PropertyId = 47
 AND PropertyValue = 1)

Upvotes: 1

Paige Haas
Paige Haas

Reputation: 11

Your sub query selects rows where the PropertyID is 47, so when its passed up to the top query, there are no records with a PropertyID of 49. You do not need the sub query, you just need a single where clause;

where PropertyID = 47 and PropertyID = 49 and PropertyValue = 1

Upvotes: 0

Related Questions