SQL Append query

Question

Please help, I'm having two tables Table_A and Table_B below, I would like to append data from Table_B to Table_A where Table_A.C1 = Table_B.C1. The Table_A final result below.

Table_A 
C1      | C2      
-------- ---------
1@a.com | e@a.com 


Table_B 
C1      | C2      
-------- ---------
1@a.com | abc@.com 

**FINAL RESULT**

Table_A 
C1      | C2      
-------- ---------
1@a.com | e@a.com; abc@.com

Also, what if c2 column's data is same in both the tables. do you still want append or just when it's different? — Gurwinder Singh, Dec 08 '16 at 18:23

score 1 · Accepted Answer · answered Dec 08 '16 at 18:12

1

update ta
set ta.c2 = ta.c2 + ';' + tb.c2
from Table_A ta
inner join Table_B tb on tb.C1 = ta.c1

answered Dec 08 '16 at 18:12

Michael Rudner Evanchik

867
1
8
19

That's non-standard SQL – Dec 08 '16 at 18:19
did he ask for ANSI compliant? Table_A is a Microsoft default, i can also tell by his copied output. it worked for him it seems....chilllllll out – Michael Rudner Evanchik Dec 08 '16 at 18:21
The `sql` tag refers to standard SQL. You should at least have stated for which DBMS this is – Dec 08 '16 at 18:32
@jp2631 next time tag TSQL or the neo community will eat you alive – Michael Rudner Evanchik Dec 09 '16 at 19:32

score 0 · Answer 2 · edited May 23 '17 at 12:30

The first thing I would recommend is good database normalization, which may make the problem you are trying to solve moot. See Database Normalization

If your database is as normalized as possible, a better idea than what you have would be to leave Table A alone and create a view with rows made up of "matches" between Table A and Table B. It is very bad database form to have multiple values in a column.

Use: CREATE VIEW table_A_table_B AS SELECT * FROM Table_A JOIN Table_B ON Table_B.C1 = Table_A.C1.

The advantage of a view is that the view "table" will update every time either Table_A or Table_B is updated.

score 0 · Answer 3 · answered Dec 08 '16 at 18:32

0

You can use ANSI MERGE:

MERGE table_a a
USING table_b b
ON a.c1 = b.c1
WHEN matched THEN
UPDATE SET a.c2 = a.c2 || ';' || b.c2;

answered Dec 08 '16 at 18:32

Gurwinder Singh

38,557
6
51
76

SQL Append query

3 Answers3