Changing this INSERT with a JOIN into a UPDATE

Question

We need to stop populating a table with records, and just update 1 row per record repeatedly. There is no need to maintain any history. I've worked with this UPDATE statement but can't get the right syntax to take into account the JOIN as well.

Can anyone help with making this INSERT INTO a simple UPDATE? I will then add IF EXISTS (do the UPDATE) ELSE (do the insert) once I figure out the syntax of the UPDATE statement. I'm using a excellent script by sqlfool.com, but have made several modifications and I hope this is the last one. Thank you !!

Insert Into [testCentral].[ReplMonitor].[dbo].[replMonitor]
(
      monitorDate
    , publicationName
    , publicationDB
    , iteration
    , tracer_id
    , distributor_latency
    , subscriber
    , subscriber_db
    , Loc_ID
    , subscriber_latency
    , overall_latency
)
Select 
      @currentDateTime
    , @publicationToTest
    , @publicationDB
    , iteration
    , tracer_id
    , IsNull(distributor_latency, 0)
    , subscriber
    , subscriber_db
    , b.LOC_ID
    , IsNull(subscriber_latency, 0)
    , IsNull(overall_latency, 
        IsNull(distributor_latency, 0) + IsNull(subscriber_latency, 0))
From @tokenResults AS a
JOIN LotteryRetail.dbo.casino b
ON 1 = 1 ;

See this, may help: http://stackoverflow.com/questions/982919/sql-update-query-using-joins/982947#982947 — gbn, Jul 19 '11 at 19:45
take a look at this one: http://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server — Pavel Veller, Jul 19 '11 at 19:51
Thanks, but Im' not able to get it to work as intended with the join, keep getting errors : — Ali Razeghi - AWS, Jul 19 '11 at 19:54

t-clausen.dk · Answer 1 · 2011-07-19T21:13:08.070

You should consider using MERGE for this. But here is a solution with your data. Unfortunately I dont know how you want to join your tables. So you need to fill that out yourself. Along with the columns which should be obvious.

update c
set monitorDate = @currentDateTime,
    publicationName = @publicationToTest
-- fill out the rest of your columns.
from
[testCentral].[ReplMonitor].[dbo].[replMonitor] c
join 
@tokenResults a
on --fill out your join
join 
LotteryRetail.dbo.casino b
on --fill out your join

score 0 · Accepted Answer · answered Dec 08 '11 at 16:16

I agree with t-clausen.dk, merge will do the insert and the update in one go.

I think you're looking for something like this for the update statement but it's hard to tell without knowing what LotteryRetail.dbo.casino and @tokenResults are:

UPDATE [testCentral].[ReplMonitor].[dbo].[replMonitor]
SET monitorDate = @currentDateTime
    ,publicationName = @publicationToTest
    ,publicationDB = @publicationDB
    ,iteration = b.iteration
    ,tracer_id = b.tracer_id
    ,distributor_latency = b.IsNull(distributor_latency, 0)
    ,subscriber = b.subscriber
    ,subscriber_db = b.subscriber_db
    ,Loc_ID = b.b.LOC_ID
    ,subscriber_latency = b.IsNull(subscriber_latency, 0)
    ,overall_latency = b.IsNull(overall_latency, 
    IsNull(distributor_latency, 0) + IsNull(subscriber_latency, 0))
FROM LotteryRetail.dbo.casino b

This will only work correctly if [testCentral].[ReplMonitor].[dbo].[replMonitor] has only one record otherwise you will need to add a WHERE clause to limit the update to only the record you want to change.

Changing this INSERT with a JOIN into a UPDATE

2 Answers2