BKK
BKK

Reputation: 503

MySQL Update table column comparing two tables

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

Answers (3)

Dušan Stokić
Dušan Stokić

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

Amit Verma
Amit Verma

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

Joachim Isaksson
Joachim Isaksson

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')

An SQLfiddle to test with.

...and always, back your data up before running updates given to you from random pepole on the Internet ;)

Upvotes: 2

Related Questions