Update SP - check if value exists first

So your UPDATE sproc effects multiple rows in tm? Then imho the condition AND td.vpkDeviceID = tm.vpkDeviceID in the CROSS APPLY is not sufficient.

Please post a concise and complete example. This means table DDL and sample data INSERT statements. In this case please explain also the model.

Yes it affects multiple rows

do you think I should loop through and update one at a time so it can look up previous values?

here is the create for both tables

/****** Object:  Table [dbo].[tblTrip_Master]    Script Date: 21/11/2014 12:55:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblTrip_Master](
	[ipkTripID] [int] IDENTITY(1,1) NOT NULL,
	[dStartTime] [datetime] NULL,
	[dEndTime] [datetime] NULL,
	[iMaxSpeed] [int] NULL,
	[iDistance] [float] NULL,
	[iAverageSpeed] [int] NULL,
	[iTripType] [int] NULL,
	[nStartEventInst] [numeric](18, 0) NULL,
	[nEndEventInst] [numeric](18, 0) NULL,
	[vpkDeviceID] [varchar](50) NULL,
	[DriverIDNo] [nvarchar](100) NULL,
	[ifkDriverID] [int] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO







/****** Object:  Table [dbo].[tblCommonTrackingData]    Script Date: 21/11/2014 12:55:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblCommonTrackingData](
	[ipkCommanTrackingID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
	[vSequenceID] [numeric](18, 0) NULL,
	[vpkDeviceID] [varchar](50) NULL,
	[vLongitude] [varchar](50) NULL,
	[vLatitude] [varchar](50) NULL,
	[vHeading] [int] NULL,
	[vReportID] [int] NULL,
	[vOdometer] [numeric](8, 2) NULL,
	[vVehicleSpeed] [int] NULL,
	[vTextMessage] [varchar](max) NULL,
	[ifkDriverID] [int] NULL,
	[bIsIgnitionOn] [bit] NULL,
	[vEventName] [varchar](100) NULL,
	[FuelVoltage] [float] NULL,
	[vDistance] [float] NULL,
	[bIsEngineOn] [bit] NULL,
	[IsAlert] [varchar](50) NULL,
	[dGPSDateTime] [datetime] NULL,
	[cInput1] [char](1) NULL,
	[cInput2] [char](1) NULL,
	[cInput3] [char](1) NULL,
	[cInput4] [char](1) NULL,
	[cInput5] [char](1) NULL,
	[cInput6] [char](1) NULL,
	[vTempSensor1] [varchar](20) NULL,
	[vTempSensor2] [varchar](20) NULL,
	[iRpm] [int] NULL,
	[nAltitude] [nvarchar](50) NULL,
	[iHdop] [int] NULL,
	[iBatteryBackup] [int] NULL,
	[vFuelDigital] [varchar](50) NULL,
	[vAnalog1] [varchar](20) NULL,
	[vAnalog2] [varchar](20) NULL,
	[cOutput1] [char](1) NULL,
	[cOutput2] [char](1) NULL,
	[iSoftwarePriority] [tinyint] NOT NULL DEFAULT ((0)),
	[vRoadSpeed] [varchar](10) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Select all Open in new window

as for the Model
[sp_Trip_UpdateIgnitionOff] gets called by an agent every x secs as sometimes there is a delay waiting for records to be entered into the tblCommonTrackingData table
the devices (tracking hardware) fire ignition on and ignition off
The purpose of [sp_Trip_UpdateIgnitionOff] is to marry the ignition off with correct ignition on the TRIP table
These events are unique, but currently Ignition off ID is being re-used on the trip table... so the TRIP table is getting messed up with incorrect Trip END (ignition Off) data

...does that help ?

Ok, I'll think I've found it:  You'll need to use the trip end date/time in the CROSS APPLY. E.g.

ALTER PROCEDURE [dbo].[sp_Trip_UpdateIgnitionOff]
AS
    SET NOCOUNT ON;

    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.dGPSDateTime >= tm.dStartTime
                                    AND td.dGPSDateTime <= tm.dEndTime -- Caveat: Overlapping possible.
                                    AND td.vpkDeviceID = tm.vpkDeviceID
                                    AND td.dGPSDateTime < DATEADD(SECOND, -30, SYSDATETIMEOFFSET())
                          ORDER BY  td.dGPSDateTime ,
                                    td.vSequenceId
                        ) AS td_outer
    WHERE   tm.nEndEventInst IS NULL;

Select all Open in new window

thanks

I tried that but the table is no longer being updated... there was no error when exec the SP
...just results not getting updated

The GpsDatetime of this update will actually be the dEndTime  (we use that value for dEndTime)
...not really sure if that helps?

Hi Ste5an

Actually, I dont think the TripEndDate is the issue

see here for an example of records we need to update

the issue I have is the nEndEventInst gets inserted multiple times... so i would first need to check if the ID has been used

I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.