I have a problem. There are three tables: T1, T2, T_target. There are many different columns in table T1 and T2, but I only need the identifier column from both. Of course, the T_target table has an identifier column, and another: project_No.
There are some identifiers that also appear in T1 and T2, but I do not want to duplicate between them, if the identifier appears in both tables, it should be inserted into T_target only once, but if it is already in T_target it allowed to act twice. Another criterion is that each newly inserted identifier should be a value of 21 in the "project_No" column. For example:
T1:
ID 2548 2566 2569 2843 2888 ...
T2:
ID 2557 2566 2569 2700 2913 2994 3018 5426 ...
T_target:
ID project_No 2976 1 3331 7 4049 7 5426 8 5915 3 6253 10 ...
And the result that I want to see:
T_target:
ID project_No 2548 21 2557 21 2566 21 2569 21 2700 21 2843 21 2888 21 2913 21 2976 1 2994 21 2018 21 3331 7 4049 7 5426 8 5426 21 5915 3 6253 10 ...
So, I tried this with this code (it is important to be “NOT NULL” here, because both T_target columns are the primary key):
insert into T_target (ID, project_No) select (select ID from T1 where ID is not NULL union select ID from T2 where ID is not NULL), 21 select * from T_target
Error message: "Msg 512, level 16, state 1, line 2 The subquery returned more than 1 values. This is not valid when the subquery follows = ,! =, <, <=,>,> = or when the subquery is used as an expression. Application completed.
Then I tried using the VALUES statement instead of the first SELECT and parentheses, but the error is the same.
There is a similar problem: mySQL query: how to insert using UNION? but this solution does not work for me because it indicates a syntax error between VALUE and SELECT.
Please give me your hand. Thanks!