0

I need to update 4 columns that have the same names in 3 different tables in mySql. Is there a safe way to do this rather than running 3 different statements?

Is something like update tablea, tableb, tablec set name='x', preferences = 0 etc where id = 5; possible.

The tables are different, but the column names are the same.

Andy Lester
  • 91,102
  • 13
  • 100
  • 152
jmenezes
  • 1,888
  • 6
  • 28
  • 44
  • 2
    Since you want to do this, maybe you should look at your database design. Storing the same thing in more than one place is a bad idea. – Dan Bracuk Jan 26 '14 at 04:24
  • Why just a single statement? What is the problem with 3 statements? Do you use a non-transactional storage engine? – Amir Pashazadeh Jan 26 '14 at 04:50

1 Answers1

1

If you have 4 tables all with a field called value1, then you can update all 4 tables with a single query. I don't know if it will technically be more efficient but it is possible. Below, I set up the mentioned tables then set multiple different tables' value1's to be equal to 9 if they aren't equal to 9.

Assuming we build this schema:

CREATE TABLE table1
  (
    value1 int
  );
CREATE TABLE table2
  (
    value1 int
  );
CREATE TABLE table3
  (
    value1 int
  );
CREATE TABLE table4
  (
    value1 int
  );
INSERT INTO table1
VALUES(1);
INSERT INTO table2
VALUES(2);
INSERT INTO table3
VALUES(3);
INSERT INTO table4
VALUES(4);

You can update multiple tables with the following: 

UPDATE table1, table2
SET table1.value1 = 9, table2.value1 = 9
WHERE table1.value1 != 9 OR table2.value1 != 9

SQLfiddle: http://sqlfiddle.com/#!2/3064f/5

Unless you're referring to the execution of SQL via some server-side language:

mysqlQuery("SELECT * from table1");
mysqlQuery("SELECT * from table2");
mysqlQuery("SELECT * from table3");

Which can just be written as:

mysqlQuery("SELECT * from table1; SELECT * from table2; SELECT * from table3");

I'm pretty sure. Hope that helps!

Edit: Added SQLfiddle

Alex Villa
  • 362
  • 2
  • 10