troubleshooting Question

Update SP - check if value exists first

Avatar of websss
websssFlag for Kenya asked on
DatabasesMicrosoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008SQL
7 Comments1 Solution353 ViewsLast Modified:
Hi

I have the following SP

ALTER PROCEDURE [dbo].[sp_Trip_UpdateIgnitionOff] 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	

UPDATE tm
SET nEndEventInst = td_outer.vSequenceId, dEndTime = td_outer.dGPSDateTime
FROM tblTrip_Master tm
CROSS APPLY (
    SELECT TOP (1) td.vSequenceId, td.dGPSDateTime
    FROM tblCommonTrackingData td
    WHERE
        td.vReportId = 8 and
        --td.vSequenceId > tm.nStartEventInst AND
		td.dGPSDateTime >= tm.dStartTime and
		td.vpkDeviceID = tm.vpkDeviceID and 
		td.dGPSDateTime < DATEADD(SECOND, -30, SYSDATETIMEOFFSET())

    --ORDER BY td.vSequenceId
	ORDER BY td.dGPSDateTime, td.vSequenceId
) AS td_outer
WHERE
    tm.nEndEventInst IS NULL
	

END

However, i've noticed an error in my data ... nEndEventInst is being put in multiple rows
It should only appear once in the table
What i need to say is something like this


WHERE
    tm.nEndEventInst IS NULL

--here is the bit i think needs changing
AND 
--do any rows with tblTrip_Master.nEndEventInst  already exist (also need to pass in [vpkDeviceID] with this)
(if exists: Select count(*) from tblTrip_Master where tblTrip_Master.vpkDeviceID  = td.vpkDeviceID AND  tblTrip_Master.nEndEventInst = tm.nEndEventInst
If COUNT  = 0 then proceed with update
Else - exit without updating


how might I structure this?
ASKER CERTIFIED SOLUTION
ste5an
Senior Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros