CODEWITHSUNDEEP
sql-servert-sqlmerge
Gavin Coates
Gavin Coates

Reputation: 1425

T-SQL Merge Returning Multiple Results

I have two databases that I am in the progress of merging into a single database. Now that they are in a single database, I can link them and use references to improve performance, however first I need to look up the ID from one table, and insert it into the other, as there is currently no foreign key.

The table structure is as follows:

Sighting: Registration, FrameDeliveryDate, RegistrationID

Registrations: ID, RegistrationNo, DeliveryDate

Currently a combination of the registration and delivery date is used to identify records.

I have tried the following statement:

MERGE INTO Sightings
USING Registrations
  ON Sightings.Registration = Registrations.RegistrationNo AND Sightings.FrameDeliveryDate = Registrations.DeliveryDate 
WHEN MATCHED THEN
UPDATE 
  SET RegistrationID = Registrations.ID;

Unfortunately, this fails as sometimes the combination returns more than one record. In such a case I would prefer to ignore linking them, although picking the first record would also be acceptable.

How can this be achieved?

Upvotes: 2

Views: 58

Answers (1)

Martin Smith
Martin Smith

Reputation: 453067

To ignore these combinations with more than one match and hence ambiguous values you can use a derived table or CTE.

WITH CTE1 AS
(
SELECT *,
       COUNT(*) OVER (PARTITION BY RegistrationNo, DeliveryDate) AS Cnt
FROM Registrations
), CTE2 AS
(
SELECT *
FROM CTE1
WHERE Cnt = 1
)
    MERGE INTO Sightings
    USING CTE2 Registrations
      ON Sightings.Registration = Registrations.RegistrationNo AND Sightings.FrameDeliveryDate = Registrations.DeliveryDate 
    WHEN MATCHED THEN
    UPDATE 
      SET RegistrationID = Registrations.ID;

Upvotes: 2

Related Questions