Merge - Without insert when not matched?

Database
Enthusiast

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.

7 REPLIES
Senior Apprentice

Re: Merge - Without insert when not matched?

Yep, simply omit the WHEN NOT MATCHED part.

Enthusiast

Re: Merge - Without insert when not matched?

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

Enthusiast

Re: Merge - Without insert when not matched?

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

Senior Apprentice

Re: Merge - Without insert when not matched?

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

Enthusiast

Re: Merge - Without insert when not matched?

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

Enthusiast

Re: Merge - Without insert when not matched?

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

Teradata Employee

Re: Merge - Without insert when not matched?

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.