Mysql – Updating multiple tables with similar structure in one query in thesql

MySQLupdate

I have multiple tables "100+", with the exact similar structure, yes I know I probably should not have it this way but it is what it is now.

What I want to do is, update all tables in one query without having to specify the table to each field.

Like so:

update table1,table2,tableN set field1 = "value" WHERE id = 1;

This of course throws an error "field1 is ambiguous", so I could do it like so:

update table1,table2 set table1.field1 = "value", table2.field1 = "value" WHERE table1.id = 1 AND table2.id = 1;

While this works, I'd rather not have a query that has 100+ table names and 100+ "ands" in the condition statements, I don't even know what's the limit on this thing.

Assuming there is no other way to do it, would it be better performance-wise, to loop through tables names and perform 100 queries instead?

Thanks

Best Answer

The solution might look like:

CREATE PROCEDURE update_all_tables (IN id BIGINT, IN val VARCHAR(64))
BEGIN
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @done = 1;
    DECLARE cur CURSOR FOR SELECT table_name 
                           FROM information_schema.tables
                           WHERE table_schema = 'MyDatabase'
                           AND table_name LIKE 'table%';
    OPEN cur;
    LOOP
        @done := 0;
        FETCH cur INTO @table;
        IF @done THEN LEAVE LOOP;
        SET @sql := CONCAT('UPDATE ',@table,' SET field1 = ? WHERE id = ?');
        PREPARE stmt FROM @sql;
        EXECUTE stmt USING val, id;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END;

PS. Don't forget DELIMITER statement...

Related Question