CODEWITHSUNDEEP
mysql
Sam Edward
Sam Edward

Reputation: 309

How can I update a table using MySQL while using search in another table

Table panel & panelists have 1 column in common. called user_id where it's an integer for example

table panel:

---------------------------------------------------------
user_id         email                   name
---------------------------------------------------------
  5              [email protected]        john
  6              [email protected]           Ronn
  1              [email protected]          Sat

table panelists:

---------------------------------------------------------
user_id         email_active           
---------------------------------------------------------
  5              1
  6              1
  1              1

what I need to do is to change the name where the email include "@dummy.com" into dummy and to change the email_active to all dummy email in the panel to 2 instead of 1.

I'm not sure how to get this done I've managed to change the name in panelist by searching for the dummy emails using

UPDATE panelists SET name = 'dummy' WHERE u_email LIKE '%dummy.com';

but I'm not sure how to update the email_active in the panel table.

Upvotes: 4

Views: 39

Answers (3)

DirWolf
DirWolf

Reputation: 891

You can change 2 tables from 1 query like this:

UPDATE panelists as a
JOIN panel as s
ON s.uid = a.uid
SET a.pstatus = '2', s.u_name = 'dummy'
WHERE s.u_email LIKE '%dummy.com';

Upvotes: 3

cn0047
cn0047

Reputation: 17091

You can change 2 tables from 1 query like this:

UPDATE panel t1, panelists t2
SET t1.email = "dummy", t2.email_active = 2
WHERE t1.user_id = t2.user_id AND t1.email like "%@dummy.com";

But in case your tables are big (contains many many rows) I'd rather advise you to run 2 separate queries, like this:

-- step 1
UPDATE panel SET email = "dummy" WHERE email like "%@dummy.com";

-- step 2
UPDATE panelists t1
JOIN panel t2 on t1.user_id = t2.user_id AND t2.email = "dummy"
SET email_active = 2;

Upvotes: 2

Romain B.
Romain B.

Reputation: 654

UPDATE panelists 
SET email_active = '2' 
WHERE user_id in (
  SELECT user_id
  FROM panel 
  WHERE email LIKE '%dummy.com'
);

This should do the job

You can use a join too like this

UPDATE panelists
JOIN panel
ON panelists.user_id = panel.user_id
SET panelists.email_active = '2'
WHERE panel.email LIKE '%dummy.com';

Upvotes: 1

Related Questions