0

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

  • But why do you have 100 tables with the same structure? That is usually a bad idea -- you found one of the reasons. – Rick James Jun 6 '18 at 1:29
1

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...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.