CODEWITHSUNDEEP
phpmysqldatabase
Ehsan
Ehsan

Reputation: 12959

Update multi tables in one query

I have 2 tables in mysql(Version:mysql 5.7.24) like this :

     table1                                   table2
    ------------------------------------------------------

     Name                                Name     Family
    ------                              -----------------

    Ali                                  Reza      Tab

    Hamid                                Ali       Rtg

I want update Name column in two table in 1 query so use this command:

$query = "
UPDATE table1
     , table2 
   SET table1.Name = 'hhh'
     , table2.Name = 'hhh' 
 WHERE table1.Name ='Hamid' 
   AND table2.Name = 'Hamid';

I expect the table1 to be updated but none of the tables are updated.Why?

If use WHERE table1.Name ='Ali' AND table2.Name ='Ali'; instead of WHERE table1.Name ='Hamid' AND table2.Name ='Hamid'; both tables are updated

Upvotes: 0

Views: 40

Answers (2)

forpas
forpas

Reputation: 164099

Change the AND operator to OR in the WHERE clause and use IF() function to do the UPDATE: 

UPDATE table1, table2
SET 
  table1.Name = IF(table1.Name = 'Hamid', 'hhh', table1.Name),
  table2.Name = IF(table2.Name = 'Hamid', 'hhh', table2.Name)
WHERE table1.Name = 'Hamid' OR table2.Name = 'Hamid';

See the demo.
Results:

table1

| Name |
| ---- |
| Ali  |
| hhh  |

table2

| Name | Family |
| ---- | ------ |
| Reza | Tab    |
| Ali  | Rtg    |

Upvotes: 2

Barmar
Barmar

Reputation: 780994

The problem is that you're using a cross-product, which has an empty result if either of the tables has no matches for its condition. It works for Ali because there are rows in both tables.

You need to use a LEFT JOIN.

UPDATE table1
LEFT JOIN table2 ON table2.Name = table1.Name
SET table1.Name = 'hhh', table2.Name = 'hhh'
WHERE table1.Name = 'Hamid'

I'm assuming that table1 is the master table, and table2 is the dependent table.

If this is a foreign key, you can use ON UPDATE CASCADE in the foreign key specification. Then you only have to update table1 and it will automatically propagate to table2.

Upvotes: 2

Related Questions