How to find Match Records in SQL and if found then update

Question

I am looking for a way to loop through a SQL table and compare it to another table and if two of the columns values are a match then update the record in one table.

I have two tables HplcData and DeSchedule, Deschedule has all the future records that the HplcData table will have. I am wanting a SQL query that will look in the HplcData for matching records in the Deschedule table and if there is update the record in Deschedule with the data from HplcData and mark in done. A match is defined by having the same BatchId and SampleAge.

How can I acomplish this?

Hplc Data

BatchID | SampleAge | Ethanol | Glucose
7254       20          7.8       4.5

DeSchedule

BatchID | SampleAge | Ethanol | Glucose | SampleCompleted
7254       20          Null       Null      Null
7254       30          Null       Null      Null

So as of right now I have a trigger that does this but I need a way to reconcile the data if the trigger misses a record and I will be running this query are a windows job.

This is the query I currently use for the trigger:

ALTER TRIGGER [dbo].[Trigger_HPLC_update_details_DeSchedule] ON [dbo].[HplcData] 
    AFTER INSERT 
AS 
BEGIN
    DECLARE @BatchId int, @Ethanol varchar(10), @Glucose varchar(10), @SampleAge varchar(10);
    SELECT @BatchId = bd.[BatchID],@Ethanol = [Ethanol], @Glucose= [DP1Glucose], @SampleAge = bd.SampleAge  
    from INSERTED bd
    update [dbo].[DeSchedule] SET [Ethanol] = @Ethanol, [Glucose] = @Glucose, [SampleCompleted] = 1 
    WHERE [BatchID] = @BatchId AND [SampleAge] = @SampleAge
END

Possible duplicate of [SQL update from one Table to another based on a ID match](http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match) — xQbert, Nov 10 '16 at 16:17

score 1 · Accepted Answer · answered Nov 10 '16 at 16:20

You can use MERGE statement.

Something along:

MERGE INTO dbo.DeSchedule as Target USING (dbo.HplcData) as Source
ON Source.BatchID = Target.BatchID and Source.SampleAge = Target.SampleAge
WHEN MATCHED
UPDATE Ethanol = source.Ethanol, Glucose = source.Glucose, SampleCompleted = 1;

score 0 · Answer 2 · answered Nov 10 '16 at 16:21

0

Using the link in the possible duplicate:

UPDATE DeSchedule A
SET A.Ethanol = HP.Ethanol,
    A.Glucose = HP.Glucose
FROM HPLCdata HP
INNER JOIN DeSchedule DS
   on HP.BatchID = DS.BatchID
  and HP.SampleAge = DS.SampleAge

answered Nov 10 '16 at 16:21

xQbert

34,733
2
41
62

How to find Match Records in SQL and if found then update

2 Answers2