CODEWITHSUNDEEP
sql
gags
gags

Reputation: 345

select sql query to merge results

I have a table old_data and a table new_data. I want to write a select statement that gives me

  1. Rows in old_data stay there
  2. New rows in new_data get added to old_data
  3. unique key is id so rows with id in new_data should update existing ones in old_data

I need to write a select statement that would give me old_data updated with new data and new data added to it.

Example:

Table a:

id         count

1             2
2            19
3             4

Table b:

id         count

2            22
5             7

I need a SELECT statement that gives me 

id         count

1             2
2            22
3             4
5             7

Upvotes: 1

Views: 84

Answers (3)

Mytroy2050
Mytroy2050

Reputation: 198

You have to use UPSERT to update old data and add new data in Old_data table and select all rows from Old_data. Check following and let me know what you think about this query

UPDATE [old_data]
SET [count] = B.[count]
FROM [old_data] AS A
INNER JOIN [new_Data] AS B
ON A.[id] = B.[id]

INSERT INTO [old_data]
           ([id]
           ,[count])
SELECT A.[id]
     ,A.[count]
FROM [new_Data] AS A
LEFT JOIN [old_data] AS B 
ON A.[id] = B.[id]     
WHERE B.[id] IS NULL

SELECT *
FROM [old_data]

Upvotes: 0

kjmerf
kjmerf

Reputation: 4335

I think this would work pretty neatly with COALESCE:

SELECT a.id, COALESCE(b.count, a.count)
FROM a
FULL OUTER JOIN b
ON a.id = b.id

Note - if your RDBMS does not contain COALESCE, you can write out the function using CASE as follows:

SELECT a.id,
CASE WHEN b.count IS NULL THEN a.count
ELSE b.count END AS count
FROM ...

You can write a FULL OUTER JOIN as follows:

SELECT *
FROM a
LEFT JOIN b
ON a.id = b.id
UNION ALL
SELECT *
FROM b
LEFT a
ON b.id = a.id

Upvotes: 2

Tedo G.
Tedo G.

Reputation: 1565

Based on your desired results:

        SELECT
             *
        FROM
             [TableB] AS B
        UNION ALL
        SELECT
             *
        FROM
             [TableA] AS A
        WHERE
             A.id NOT IN (SELECT id FROM [TableB])

Upvotes: 2

Related Questions