MySQL, Update Multiple Tables With One Query

Update multiple tables in a single query in mysql

You can try below code:

UPDATE tab1, tab2, tab3
SET tab1.a = '', tab2.b = '',tab3.c = ''
WHERE tab1.id = 3 AND tab2.id = 9 AND tab3.id = 5;

UPDATE:

As per mentioned by OP, the code not working for Mysql 5.5, below code added

UPDATE tab1 a 
  INNER JOIN tab2 b ON (a.id = b.id)
  INNER JOIN tab3 c ON (a.id = c.id)
SET tab1.a = '', tab2.b = '', tab3.c = ''
WHERE a.id = 3 AND tab2.id = 9 AND tab3.id = 5;

How to UPDATE multiple tables with 1 query in MYSQL

You can do this without the explicit INNER JOIN syntax, but instead do it in your WHERE clause:

UPDATE blog_posts a, search b
SET a.postTitle = :postTitle
    , b.title = :postTitle
    , a.postSlug = :postSlug
    , b.link = :postSlug
    , a.postDesc = :postDesc
    , b.description = :postDesc
-- this continues for any tables/values you want to set
WHERE a.postID = :postID
    AND -- whatever else joins your two tables together or other unique values

Update multiple tables in one query, in MySQL

As per comments above, the UPDATE reports zero rows affected if there is no net change. So if the tables are already updated with the desired values, the UPDATE is a no-op and no rows are "affected."

How can i update multiple table with one query php?

No need for a transaction because it seems your PDO does not accept a transaction.

Something like this should work in the example you gave:

  UPDATE tb1, tb2
    SET tb1.aName = $aName,
        tb2.bName = $bName
    WHERE
        tb1.id_a = $id_a
        AND tb2.id_b = $id_b;

mysql update multiple records of multiple tables using one statement based on condition

This should do it:

update dbo.games as ga, dbo.users as us
set 
    ga.state = 1, 
    us1.score = us1.score + case 
        when 
            (ga.score1 > ga.score2 and us.id = ga1.id1)
            or (ga.score2 > ga.score1 and us.id = ga2.id2)
        then 1
        else -1
    end
where 
    ga.state = 0 
    and ga.score1 <> ga.score2
    and us.id in (ga.id1, ga.id2)

The logic is to select two rows in the user table, and then do conditional logic to decide whether to add or remove a point.

Note: you did not tell how you want to handle tied competitions - so this query explicitly ignores them.

Update multiple table with inner join

UPDATE pur_order a, pur_entry b SET a.is_deleted = 1, b.is_deleted = 1
WHERE b.id_pur_order = b.id AND a.id = 1;

SQLite cannot update multiple tables at once

You can do it, but it requires trickiness. Make a view that joins the tables of interest (I assume that's a sensible thing to do) and set an INSTEAD OF UPDATE trigger on the view. Trigger programs can modify multiple different tables.

CREATE VIEW xy AS 
    SELECT X.3 AS x3, Y.3 AS y3, X.1 AS x1, X.2 AS x2 
    FROM X JOIN Y ON X.1 = Y.1 AND X.2 = Y.2;

CREATE TRIGGER xy_update
INSTEAD OF UPDATE ON xy
BEGIN
    UPDATE X SET X.3 = NEW.x3 WHERE X.1 = NEW.x1 AND X.2 = NEW.x2;
    UPDATE Y SET Y.3 = NEW.x3 WHERE Y.1 = NEW.x1 AND Y.2 = NEW.x2;
END;

Then you can do this:

UPDATE xy SET x3 = 'Updated String', y3 = 'Updated String';
-- No select required, but you can add one if you want

If you only need this for a short while, consider making xy and its trigger be TEMPORARY.

---

Related Topics