Updating multiple tables with one query

Question

I have a massive database and and need a query to update different tables in the database. I believe this should be easy since the column I'm changing is the same in every table. This is what I have so far;

UPDATE  a_assets,
        client_notes,
        client_pending,
        client_task,
        country,
        document_log,
        favlists,
        favourites,
        g_address,
        g_climst,
        g_dialog,
        g_lang,
        g_prdmst,
        g_secure,
        j_alloc,
        logger,
        passhistory,
        portfolios,
        prod_metrics_tank,
        product_usage_lists,
        region,
        reasearch_logger,
        search_dataphile,
        search_esg,
        search_rpm,
        sql_workout,
        universe_source,
        user_jurisdications,
        user_languages,
        user_universe,
        work_group_mappings,
        work_groups,
        spt_docs 
set     a_assets.planner = ? ,
        client_notes.planner = ?,
        client_pending.planner = ?,
        client_task.planner = ?,
        country.planner = ?,
        document_log.planner=?,
        favlists.planner=?,
        favourites.planner=?,
        g_address.planner = ?,
        g_climst.planner =?,
        g_dialog.planner=?,
        g_lang.planner=?,
        g_prdmst.planner=?,
        j_alloc.planner=?,
        logger.planner=?,
        passhistory.planner=?,
        portfolios.planner=?,
        prod_metrics_tank.planner=?,
        product_usage_lists.planner=?,
        region.planner=?,
        reasearch_logger.planner=?,
        search_dataphile.planner=?,
        search_esg.planner=?,
        search_rpm.planner=?,
        sql_workout.planner=?,
        universe_source.planner=?,
        user_jurisdications.planner=?,
        user_languages.planner=?,
        user_universe.planner=?,
        work_group_mappings.planner=?,
        work_groups.planner=?,
        spt_docs.planner=?,
        g_secure.planner = ? 
where   a_assets.planner = ? ,
        client_notes.planner = ?,
        client_pending.planner = ?,
        client_task.planner = ?,
        country.planner = ?,
        document_log.planner=?,
        favlists.planner=?,
        favourites.planner=?,
        g_address.planner = ?,
        g_climst.planner =?,
        g_dialog.planner=?,
        g_lang.planner=?,
        g_prdmst.planner=?,
        g_secure.planner = ?,
        j_alloc.planner=?,
        logger.planner=?,
        passhistory.planner=?,
        portfolios.planner=?,
        prod_metrics_tank.planner=?,
        product_usage_lists.planner=?,
        region.planner=?,
        reasearch_logger.planner=?,
        search_dataphile.planner=?,
        search_esg.planner=?,
        search_rpm.planner=?,
        sql_workout.planner=?,
        universe_source.planner=?,
        user_jurisdications.planner=?,
        user_languages.planner=?,
        user_universe.planner=?,
        work_group_mappings.planner=?,
        work_groups.planner=?,
        spt_docs.planner=?

Im not sure why this wont work since all tables are updating their planner column. When this runs I get an: ILLEGAL SYMBOL "token". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: token-list.

What needs to change in the query in order for all the tables to update with the same data.

Far as I know, you can only update columns in a single table in one statement. — Andrew, Jul 15 '14 at 18:46
Well, for one thing you can't use commas like that in the `WHERE` clause, it'd have to be `OR` or `AND`. If you could update multiple tables, you're currently performing a Cartesian join, which would make things... interesting. _Why_ are you attempting to update every table? It suggests that `planner` should have a foreign key to some other table, that you could then do an `ON UPDATE CASCADE` or something. Or potentially the db should be restructured, but that would be more work. — Clockwork-Muse, Jul 16 '14 at 08:39

score 0 · Accepted Answer · edited May 23 '17 at 11:50

0

You can not update more than one table in single update statement. [Update multiple tables in SQL Server using INNER JOIN

edited May 23 '17 at 11:50

Community

1
1

answered Jul 15 '14 at 18:58

Pankti Jain

74
3

1

How is this relevant for db2? – Lennart - Slava Ukraini Jul 15 '14 at 19:53

score 0 · Answer 2 · answered Jul 15 '14 at 19:52

Why do you want to update all tables in one statement? Assuming you are using a recent version of LUW you can actually do the update via transition tables and a cte according to:

with t1 (n) as ( select count(1) 
                 from new table (
                     update a_assets 
                         set planer = ?
                     where planer = ?
                 )
               )
    , t2 (n) as ( ...

               )
    , ...

    , tn (n) as ( ...

               )
 select n from t1
 union all
 select n from t2
 ...
 select n from tn

but I suspect that this is not what you want.

I would suggest that you make a procedure that makes a loop over the tables and then uses execute immediate to fire of the statement(s)

I can't decide if this should get +1 or not. It's true; but it also perhaps more than doubles the number of characters from just using the individual `update` statements, and I can't guess about relative run-time performance. It does directly answer the question, though. It's worth seeing. — user2338816, Jul 15 '14 at 23:58

Updating multiple tables with one query

2 Answers2