I want to update data in multiple tables, contain the suffix "_org_path" or "_path" value in the field as long as it is'/000000/460000/460500/460400 are changed to '/000000/460000/460400'
Because table has more than a dozen, some fields are more than one table, one change is very trouble, don't know that I can write a stored procedure to implement

CodePudding user response:

Select 'update' | | t.T ABLE_NAME | | 'set' | | tc OLUMN_NAME | | '=replace (' | | tc OLUMN_NAME | |', '/000000/460000/460500/460400 "', ' '/000000/460000/460400')
Where '| | tc OLUMN_NAME | |' like '%/000000/460000/460500/460400 "; 'the from user_tab_columns t
Where tc OLUMN_NAME like '% _PATH'
And t.T ABLE_NAME in (' to deal with the table list of ');
You use this statement to generate SQL and then execute,
Write a stored procedure,

CodePudding user response:

If dynamic SQL will be very convenient, can't, according to the way of 1/f, write down