We help IT Professionals succeed at work.
Get Started

Update SP - check if value exists first

351 Views
Last Modified: 2015-05-06
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

Open in new window


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

Open in new window



how might I structure this?
Comment
Watch Question
Senior Developer
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 7 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE