CODEWITHSUNDEEP
phpmysql
user3257571
user3257571

Reputation: 23

MySQL: Update two tables (if email is in both)

I have a table 'userdata' with a lot of rows containing the fields id, name, email, etc., IsBL and another table 'blacklist' with the fields id, group, email, xcount

I do an update with the following code:

UPDATE `userdata` AS `dest`,
         (SELECT * FROM `blacklist` WHERE `group` = "3") AS `src`
SET `dest`.`IsBL` = '1' 
WHERE `dest`.`email` = `src`.`email`

This works as expected. Furthermore I want to increase the 'xcount' field all the time a mail address is equal on both tables. Is there a way to do it?

I tried

SET `dest`.`IsBL` = '1', `src`.`xcount` = `src`.`xcount` + 1
WHERE `dest`.`email` = `src`.`email`

but this doesn't work.

Upvotes: 1

Views: 83

Answers (1)

Nick
Nick

Reputation: 147166

You can't update src because it isn't actually a table. You need to JOIN the blacklist table instead and include the WHERE group = 3 condition with the condition on email:

UPDATE `userdata` AS `dest`, `blacklist` AS `src`
SET `dest`.`IsBL` = '1', `src`.`xcount` = `src`.`xcount` + 1
WHERE `dest`.`email` = `src`.`email` AND
      `src`.`group` = 3

Upvotes: 2

Related Questions