Reputation: 503
Users table has following columns
-----------------------------------
Users isMember
A Yes
B Yes
C Yes
X Yes
Y Yes
Z Yes
ValidSubcription table the following details
-------------------------
Users isSubActive
A Yes
X Yes
Y Yes
Now based on this details I want to change the isMember column in Users table and set it from Yes to No for the users not present in ValidSubscription table.
output should be like this
Users isMember
A Yes
B No
C No
X Yes
Y Yes
Z No
Upvotes: 0
Views: 454
Reputation: 171
Without using Joins and Coalesce, you can update the required table like this:
UPDATE `users`
SET `users`.`isMember`="No"
WHERE `users`.`user` NOT IN (
SELECT `validsubscription`.`user`
FROM `validsubscription`
WHERE `validsubscription`.`isSubActive`="Yes"
);
I've changed the users
column to user
to avoid ambiguity with the users
table but everything else should be the same as in the given tables.
Upvotes: 0
Reputation: 2490
You can select the required data like this. You can insert the new selected result into your table.
UPDATE Users
SET Users.isMember = COALESCE(V.isSubActive, 'No')
FROM Users U
LEFT OUTER JOIN ValidSubcription V ON U.[User] = V.[User]
Upvotes: 0
Reputation: 180897
You can just update your table using a LEFT JOIN
, which will update all the rows in the Users table even if they don't have a corresponding line in the ValidSubscription table.
The update of isMember uses COALESCE
which will use the value of isSubActive if it exists for that row or 'No' if it doesn't.
UPDATE Users u
LEFT JOIN ValidSubscription s
ON u.Users = s.Users
SET isMember = COALESCE(isSubActive, 'No')
...and always, back your data up before running updates given to you from random pepole on the Internet ;)
Upvotes: 2