All Forums Database
teradatatester 69 posts Joined 01/10
16 Mar 2016
Merge - Without insert when not matched?

I have 2 tables.
 
One has the test locations, columns are:
Test_Date
Latitude
Longitude
 
The second table has the info about the locations, columns are:
Latitude
Longitude
Area
Region
Market
 
I altered the test locations table adding the columns Area, Region, Market.
I would like to get the new columns populated with the corresponding data from the locations table.
From what I read Merge is going to be more effient than update. Is it possible to merge without inserting if there wasn't a match found? If there is no match I don't want anything to happen.

dnoeth 4628 posts Joined 11/04
16 Mar 2016

Yep, simply omit the WHEN NOT MATCHED part.

Dieter

teradatatester 69 posts Joined 01/10
16 Mar 2016

Error: The search condition must fully specify the Target table primary index and partition column(s) and expression must match INSERT specification primary index and partition column(s)
 
What am I missing?
MERGE INTO tests as T1
USING
     (
         SELECT * FROM locations
     ) as L
ON   T1.LATITUDE=l.LATITUDE
AND T1.LONGITUDE=l.LONGITUDE
WHEN MATCHED THEN UPDATE
SET
 Area= L.Area
,Region = L.Region
,Market = L.Market
 

16 Mar 2016

it's simple.if there will be no match between the tables then NULLS (if allowed) would be getting inserted at those places

dnoeth 4628 posts Joined 11/04
16 Mar 2016

If you can't match the (P)PI in ON you can't use MERGE and must go for UPDATE instead.

Dieter

teradatatester 69 posts Joined 01/10
16 Mar 2016

Error: Target row updated by multiple source rows.
In the data it is possible to have a test at the same location multiple days. Do I need to add a check for each Test_Date in the where clause and loop through all possible dates?
 
UPDATE tests
FROM (
        SELECT * FROM locations
     ) L
SET
 Area= L.Area
,Region = L.Region
,Market = L.Market
WHERE tests.LATITUDE=L.LATITUDE
AND  tests.LONGITUDE=L.LONGITUDE
 
 

16 Mar 2016

Here the data from the test table is getting matched multiple times with the other table.
Have some unique matches in both the tables .it will solve the issue

Fred 1096 posts Joined 08/04
16 Mar 2016

The locations (source) table apparently has multiple rows with the same combination of Latitude/Longitude values. The database can't determine which one should be used to update the tests (target) table.
 

You must sign in to leave a comment.