Updating a table with a column from another table in SQL

Question

I need to create a new table from the data that I extract from the following two tables:

First table:

Var cur_number ------------------- A 10 B 8 

Second table:

 Var new_number ------------------- A 2 A 11 B 4 B 6 

The new table should contain the Var column and the Number column, where for each variable there will be one row with its cur_number number, and the remaining rows will contain numbers from the columns of the second new_number column, where new_number <cur_number. For example, in the example shown above, for A there will be one line with 10 (its cur_number) and one line with "2" (since 2 <10, however 11> 10).

in my example, the new table will be:

 Var Number A 10 A 2 B 8 B 4 B 6 

The database is very large, and runtime is critical, so I cannot use UNION for two tables ...

Answer 1

Try the following:

 SELECT var, cur_number FROM FirstTable UNION SELECT t2.var, t2.new_number FROM Firsttable t1 INNER JOIN SecondTable t2 ON t2.new_number < t1.cur_number; 

Strike>

Update: If you are using SQL Server 2008 or higher, you can use MERGE to merge the two tables into one like this:

 MERGE INTO FirstTable AS TGT USING SecondTable AS SRC ON SRC.new_number >= TGT.cur_number WHEN NOT MATCHED THEN INSERT (var, cur_number) VALUES (SRC.var, SRC.new_number); 

SQL Fiddle Demo

This will combine the values ​​of the two tables into the first table. The first table will contain:

 VAR CUR_NUMBER A 10 B 8 A 2 B 4 B 6 

Please Note: When using MERGE :

• You must complete the MERGE statement with a semicolon, this is required.
• You cannot INSERT inside WHEN MATCHED , so I used the opposite >= condition in WHEN NOT MATCHED .

Answer 2

This script assumes that there is only one entry for “Var” in table 1.

- insert everything from table 1

 insert into newtable (var,number) select var,cur_number from table1 t1 

- insert from table2, where new_number <cur_number

 insert into newtable (var,number) select t2.var, t2.new_number from table2 t2 inner join table1 t1 on t1.var = t2.var and t2.new_number < t1.cur_number 

Answer 3

If you know that you do not have duplicate values ​​in these two tables, you can use UNION ALL instead of UNION

 select * from table1 t1 UNION ALL select* from table2 t2 where t2.new_number < t1.cur_number 

By double values, I mean something like this:

 table1: var cur_number A 8 table2: var new_number A 8 

The difference is that UNION ALL faster than UNION , because UNION removes duplicates from the result set using SELECT DISTINCT . In case you have duplicates, UNION is the best in my experience.

Answer 4

Why not use Union ? * SQLFIDDLE

 select * from first union select s.var,s.new_number from second s join first f on s.var = f.var join ( select f.var, max(f.cur_number) maxn from first f group by f.var) as t on t.var = s.var where s.new_number < t.maxn order by var asc ; 

results

 VAR CUR_NUMBER A 10 B 8 A 2 B 4 B 6