Updating a column in two different tables with one sql

Question

I have two tables that are identical in structure. Table1 holds moderated data, table2 holds data that still has not been moderated.

Table 1

+------+-----------+-----------------+--------+-----------+----------+
| "id" |  "name"   |  "description"  | "type" | "country" | "status" |
+------+-----------+-----------------+--------+-----------+----------+
| "1"  | "Title 1" | "Description 1" | "1"    | "US"      | "0"      |
| "2"  | "Title 2" | "Description 2" | "1 "   | "UK"      | "0"      |
+------+-----------+-----------------+--------+-----------+----------+

Table 2


    +------+-----------+-----------------+--------+-----------+----------+
    | "id" |  "name"   |  "description"  | "type" | "country" | "status" |
    +------+-----------+-----------------+--------+-----------+----------+
    | "1"  | "Title 1" | "Description 1" | "1"    | "US"      | "2"      |
    | "2"  | "Title 2" | "Description 2" | "1 "   | "UK"      | "2"      |
    +------+-----------+-----------------+--------+-----------+----------+

I'm trying to update the column status in both the tables using a single sql. Actually, a moderator updates only table2 since that's the table available to him.

When table2 two gets updated, cantable1 be updated at the same time? Using a single sql? Right now, I'm using 2 different convetional sql statements for this.

Right now I do like this:

UPDATE table2 set status = 0 where id = spid and country = spcountry;//Update table2 first

        UPDATE table1 a
        INNER JOIN table2 b
        ON a.id = b.id and a.country = b.country
        SET a.status = b.status
        WHERE a.id=spid;

What I hope to do: Example

$status = 0;//php

update table1, table2 set status = $status where id=1 and conuntry = 'us' in table1 and table2.//The id and country need to be the same in both tables.

Duplicate question: http://stackoverflow.com/questions/2044467/how-to-update-two-tables-in-one-statement-in-sql-server-2005 — Nikola R., Jun 05 '13 at 04:29
This duplicate is mysql: http://stackoverflow.com/questions/8765490/mysql-update-two-tables-at-once — Ben English, Jun 05 '13 at 04:30

invisal · Accepted Answer · 2013-06-05T05:22:08.690

While you can update two tables with the following syntax

UPDATE TBL1, TBL2
SET TBL1.status = 'Blah', TBL2.status = 'blah'
WHERE TBL1.id = TBL2.id 
      AND TBL2.id = 2;

but this could be harmful. Consider the following case: when TBL2 contains row with id = 2, while TBL1 does not have a row with id = 2. It causes update to fail. In order for this to work, TBL1 and TBL2 must be exactly the same. And if those two tables are exactly the same, why bother haven't two tables in the first place?

@invisal If it fails, all that'll happen is it'll say 0 rows updated, right? It wont cause the script to stop running. – jmenezes

First, you need to make sure that those two tables have the same data:

For any insertion, you need to insert into two tables
For any update, you need to update two tables
For deletion, you need to delete from two tables

It does not stop your script from running but you need to enforce those conditions. If two table is not consistence, so sometimes update will not work. There is not much different between script that does not work as intended and script that throw error. Both of them are fail to do what they should do.

@invisal If it fails, all that'll happen is it'll say `0 rows updated`, right? It wont cause the script to stop running. — jmenezes, Jun 05 '13 at 05:13
Of course, it won't stop the script from running, but you will have inconsistent data which introduce a new bug which is hard to debug later. — invisal, Jun 05 '13 at 05:15

score 1 · Answer 2 · answered Jun 05 '13 at 04:49

Rather going to update 2 tables with a single statement(which is cumbersome), It can be a easy n safer way to use Transactions in your Query/ Stored procedure. It will ensure update for both of tables in a go.

START TRANSACTION;
UPDATE table1 SET summary=@A WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

hope this help !!

peterm · Answer 3 · 2013-06-05T04:38:28.743

0

You can achieve your goal with a trigger on table2

CREATE TRIGGER tg_table2_update
AFTER UPDATE ON table2 
FOR EACH ROW
  UPDATE table1
     SET status = NEW.status
   WHERE id = NEW.id AND country = NEW.country;

Here is SQLFiddle demo.

edited Jun 05 '13 at 04:38

answered Jun 05 '13 at 04:33

peterm

91,357
15
148
157

I'm sorry I should have mentioned I cannot use triggers. There already is one. – jmenezes Jun 05 '13 at 04:36
@jmenezes You can probably add this update to your existing trigger. Show your trigger and will try to sort it out. – peterm Jun 05 '13 at 04:43
My existing trigger is also called after update. This one is done for new posts. I'm trying to get reid of all the triggers, because table2 gets so many updates. `BEGIN IF NEW.STATUS = 0 THEN INSERT INTO table1 VALUES(NULL,NEW.name,NEW.description,NEW.type,NEW.country); END IF; END` – jmenezes Jun 05 '13 at 04:47

score 0 · Answer 4 · answered Jun 05 '13 at 04:46

This'll probably do it, but I haven't tested it:

begin transaction t1;

UPDATE table2 
    set status = 0 
    where id = spid and country = spcountry;

UPDATE table1 a INNER JOIN table2 b ON a.id = b.id and a.country = b.country 
    SET a.status = b.status 
    WHERE a.id=spid;

commit transaction t1;

Updating a column in two different tables with one sql

4 Answers4