In a database, there are 11 table (t1... T11), 11 table there are three of the same field. A, b, and now want to according to the value of a and b two fields t.a=x and t.b like % y %, to select 11 all conform to the conditions of the data in the table, and then update the set t.a=z where to meet the above query data,
Select t1. The from t1 where a t1. A=x and t1. Like b y % %, then update the set t1. A=z;
Preliminary idea is to make a loop to update, but does not support SQL array, collection and inappropriate, t.a (x), t.b (y), t.a (z) value is a collection, is 6000, is simply a query based on conditions in the t.a and t.b t.a all tables, and then update t.a
What is the good solution? Single table data in about 20000 lines,

CodePudding user response:

Can take the 11 table by combining several of the same field and the primary key in a table

CodePudding user response:

Using the merge into t
(
Select a, b, z from t1 where a... And b...
Union all
Select a, b, z from t2 where a... And b...
.
Union all
Select a, b, where a z from t11... And b...
) u
On (t.a=u.a and t.b=u.b)
The when matched then
Update the set t.a=u.z;
Probably on this train of thought,