1

I have two tables x and y contains two columns each say (email and password) in my database.

I am not able understand how to update password in a table using where (email=...) clause so that if email belongs to x table then it's password get updated or if email belongs to y table then it's password get updated.

I tried in this way where the given email belongs to x table so that first query executed .

UPDATE x SET password="qas" where email="adsf@gmail.com";
UPDATE y SET password="qas" where email="adsf@gmail.com";

I am able to update separate (two) queries but i want single query which checks email in both tables and get password updated.

Cosmin Staicu
  • 1,809
  • 2
  • 20
  • 27
Abhishek Bommena
  • 53
  • 4
  • 1
    'i want single query' - not possible - you could make life difficult for yourself by puttng the process into a stored procedure and testing for existence in x or y. Or fix your db so that you have only 1 table. – P.Salmon Sep 06 '20 at 08:25
  • You are storing the same kind of informations in 2 separate tables, this smells poorly designed DB. [DRY !](https://en.wikipedia.org/wiki/Don%27t_repeat_yourself). [Database Normalization](https://en.wikipedia.org/wiki/Database_normalization) – Cid Sep 06 '20 at 08:30
  • can you combine the two tables ? – SebNik Sep 06 '20 at 09:02

2 Answers2

0

If the 2 tables are relatable then you can update using join:

UPDATE x 
JOIN y ON x.email = y.email
SET x.password = "qas", y.password = "qas" 
where x.email="adsf@gmail.com";

If the 2 tables are not relatable then you cannot update in a single query

aRvi
  • 2,203
  • 1
  • 14
  • 30
0

The below query will work in your case:

update X
join Y
set 
    X.password = if(X.email = 'adsf@gmail.com', 'qas' , X.password),
    Y.password = if(Y.email = 'adsf@gmail.com', 'qas' , Y.password)
where 
    X.email = 'adsf@gmail.com' or
    Y.email = 'adsf@gmail.com'
;

But I'm not sure about query performance

Live fiddle here

Slava Rozhnev
  • 9,510
  • 6
  • 23
  • 39