Only update rows changed 1

[jadams0173]

Using sql 2000

I need to update only rows that have changed data. The incoming data is from a VB front end that passes parameters to the SP. I currently update the rows whether they need it or not, but this is causing the LAST_UPDATE field to be changed when none of the other data changes.

I could do this in the front end, but would rather do it in sql (unless this is a really bad practice). I've got my VB forms and code nailed down and would like to not have to change it, but I can if that's what I should do.

I thought of loading the incoming data into a #temp table and join to the current data to see if it changed. I'm also open for suggestions. This is my current SP

[COLOR=green]/*
[/color][COLOR=green]this will update or insert data saved from the front end into the database
[/color][COLOR=green]*/[/color]

[COLOR=blue]ALTER[/color]           [COLOR=blue]procedure[/color] USP_Save_Data_Master_Scheduler
[COLOR=green]--THIS WILL BE THE SEQUENCE IN WHICH THEY ARE IN THE LV FOR EACH TAB
[/color]@MO [COLOR=blue]Char[/color](8),
@ITEM [COLOR=blue]CHAR[/color](20),
@SCHED_DATE [COLOR=#FF00FF]DATETIME[/color],
[COLOR=green]--@LINE CHAR(5),
[/color]@PRIORITY [COLOR=blue]INT[/color], 
@PASS [COLOR=blue]SMALLINT[/color],
@COMMENT [COLOR=blue]VARCHAR[/color](500),
@SCHEDULED_QTY [COLOR=blue]INT[/color],
[COLOR=green]--@RUN_QTY INT,
[/color]@STATUS [COLOR=blue]INT[/color],
@SCHEDULED_LINE [COLOR=blue]INT[/color],
@OTHER_SIDE [COLOR=blue]VARCHAR[/color](6)

[COLOR=blue]AS[/color]

[COLOR=blue]Set[/color] [COLOR=#FF00FF]NOCOUNT[/color] [COLOR=blue]ON[/color]

[COLOR=blue]DECLARE[/color] @UPDATEID [COLOR=blue]AS[/color] [COLOR=blue]INT[/color],
	@COST_FMLY [COLOR=blue]CHAR[/color](5)

[COLOR=blue]SET[/color] @UPDATEID = (
				[COLOR=blue]SELECT[/color] MYID 
				[COLOR=blue]FROM[/color] SCHEDULE_DATA
				[COLOR=blue]WHERE[/color] MO = @MO
				AND PASS = @PASS
		)
[COLOR=blue]print[/color] @UPDATEID

[COLOR=green]--SEE IF WE HAVE AN ID. IF SO WE NEED TO UPDATE
[/color][COLOR=blue]IF[/color] [COLOR=#FF00FF]COALESCE[/color](@UPDATEID,0) > 0 
	[COLOR=blue]BEGIN[/color]
		[COLOR=green]--UPDATE THE RECORD FOR THE ID
[/color]		[COLOR=blue]UPDATE[/color] SCHEDULE_DATA
		[COLOR=blue]SET[/color] SCHED_DATE = @SCHED_DATE ,
			SCHEDULED_LINE = @SCHEDULED_LINE ,
			PRIORITY = @PRIORITY ,
			LAST_UPDATE = [COLOR=#FF00FF]GETDATE[/color]() ,
			[COLOR=blue]COMMENT[/color] = @COMMENT ,
			STATUS = @STATUS ,
			REL_DTS = [COLOR=blue]CASE[/color] @STATUS
					[COLOR=blue]WHEN[/color] 0 [COLOR=blue]THEN[/color] [COLOR=#FF00FF]GETDATE[/color]()
					[COLOR=blue]WHEN[/color] 4 [COLOR=blue]THEN[/color] [COLOR=#FF00FF]GETDATE[/color]()
					[COLOR=blue]ELSE[/color] null
				   [COLOR=blue]END[/color]
			[COLOR=green]--SCHEDULED_LINE = @SCHEDULED_LINE
[/color]		[COLOR=blue]WHERE[/color] MYID = @UPDATEID
	[COLOR=blue]END[/color]
		
[COLOR=blue]ELSE[/color]
	[COLOR=green]--THIS IS A NEW RECORD SO INSERT THE DATA
[/color]	[COLOR=blue]BEGIN[/color]
		[COLOR=green]--GET THE COST FAMILY
[/color]		[COLOR=blue]SET[/color] @COST_FMLY = [COLOR=#FF00FF]reverse[/color]([COLOR=#FF00FF]left[/color](dbo.getnumbers([COLOR=#FF00FF]reverse[/color]([COLOR=#FF00FF]RTRIM[/color](@ITEM))),3))

		[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] SCHEDULE_DATA
                      (	
					MO,
					ITEM, 			
					[COLOR=green]--ORDER_QTY, 
[/color]					SCHED_DATE,
					PRIORITY, 
					PASS, 
					LAST_UPDATE, 
					[COLOR=blue]COMMENT[/color], 
					COST_FMLY, 
                      			SCHEDULED_QTY, 
					[COLOR=green]--RUN_QTY, 
[/color]					STATUS, 
					SCHEDULED_LINE,
					OTHER_SIDE,
					REL_DTS
				)
[COLOR=blue]VALUES[/color]     (
			@MO,
			@ITEM,
			[COLOR=green]--@ORDER_QTY,
[/color]			@SCHED_DATE,
			@PRIORITY,
			@Pass,
			[COLOR=#FF00FF]GETDATE[/color](),[COLOR=green]--THIS WILL BE THE SYSTEM DATE AND TIME
[/color]			@COMMENT,
			@COST_FMLY,
			@SCHEDULED_QTY,
			[COLOR=green]--@RUN_QTY,
[/color]			@STATUS,
			@SCHEDULED_LINE,
			@OTHER_SIDE,
			[COLOR=blue]CASE[/color] @STATUS
				[COLOR=blue]WHEN[/color] 0 [COLOR=blue]THEN[/color] [COLOR=#FF00FF]GETDATE[/color]()
				[COLOR=blue]WHEN[/color] 4 [COLOR=blue]THEN[/color] [COLOR=#FF00FF]GETDATE[/color]()
				[COLOR=blue]ELSE[/color] NULL
			[COLOR=blue]END[/color] 
		)		

	[COLOR=blue]END[/color]

 

[RiverGuy]

What kind of VB? If you're using VB.Net, then ADO.Net has this built in. Your .Net DataSet and DataTable objects will keep track of which rows have changed, and you only pass those in to your stored procedure to be updated.

 

[jadams0173]

VB6

 

[gmmastros]

Maybe something like this...

ALTER           procedure USP_Save_Data_Master_Scheduler
--THIS WILL BE THE SEQUENCE IN WHICH THEY ARE IN THE LV FOR EACH TAB
@MO Char(8),
@ITEM CHAR(20),
@SCHED_DATE DATETIME,
--@LINE CHAR(5),
@PRIORITY INT,
@PASS SMALLINT,
@COMMENT VARCHAR(500),
@SCHEDULED_QTY INT,
--@RUN_QTY INT,
@STATUS INT,
@SCHEDULED_LINE INT,
@OTHER_SIDE VARCHAR(6)

AS

Set NOCOUNT ON

DECLARE @UPDATEID AS INT,
    @COST_FMLY CHAR(5)

If Exists(  SELECT MYID
            FROM   SCHEDULE_DATA
            WHERE  MO = @MO
                   AND PASS = @PASS)
  Begin
    -- Data exists, is it different?
    If Not Exists(SELECT MYID
                  FROM   SCHEDULE_DATA
                  WHERE  MO = @MO
                         AND PASS = @PASS
                         And SCHED_DATE = @SCHED_DATE
                         AND SCHEDULED_LINE = @SCHEDULED_LINE
                         AND PRIORITY = @PRIORITY
                         AND COMMENT = @COMMENT
                         AND STATUS = @STATUS)
      Begin

        SET @UPDATEID = (
                         SELECT MYID
                         FROM   SCHEDULE_DATA
                         WHERE  MO = @MO
                                AND PASS = @PASS
                         )
        --UPDATE THE RECORD FOR THE ID
        UPDATE SCHEDULE_DATA
        SET SCHED_DATE = @SCHED_DATE ,
            SCHEDULED_LINE = @SCHEDULED_LINE ,
            PRIORITY = @PRIORITY ,
            LAST_UPDATE = GETDATE() ,
            COMMENT = @COMMENT ,
            STATUS = @STATUS ,
            REL_DTS = CASE @STATUS
                    WHEN 0 THEN GETDATE()
                    WHEN 4 THEN GETDATE()
                    ELSE null
                   END
            --SCHEDULED_LINE = @SCHEDULED_LINE
        WHERE MYID = @UPDATEID
    END
        
ELSE
    --THIS IS A NEW RECORD SO INSERT THE DATA
    BEGIN
        --GET THE COST FAMILY
        SET @COST_FMLY = reverse(left(dbo.getnumbers(reverse(RTRIM(@ITEM))),3))

        INSERT INTO SCHEDULE_DATA
                      (    
                    MO,
                    ITEM,             
                    --ORDER_QTY,
                    SCHED_DATE,
                    PRIORITY,
                    PASS,
                    LAST_UPDATE,
                    COMMENT,
                    COST_FMLY,
                                  SCHEDULED_QTY,
                    --RUN_QTY,
                    STATUS,
                    SCHEDULED_LINE,
                    OTHER_SIDE,
                    REL_DTS
                )
VALUES     (
            @MO,
            @ITEM,
            --@ORDER_QTY,
            @SCHED_DATE,
            @PRIORITY,
            @Pass,
            GETDATE(),--THIS WILL BE THE SYSTEM DATE AND TIME
            @COMMENT,
            @COST_FMLY,
            @SCHEDULED_QTY,
            --@RUN_QTY,
            @STATUS,
            @SCHEDULED_LINE,
            @OTHER_SIDE,
            CASE @STATUS
                WHEN 0 THEN GETDATE()
                WHEN 4 THEN GETDATE()
                ELSE NULL
            END
        )        

    END

Basically...

[tt]
If Exists(Row in table)
Begin
If Not Exists(Row in table matching all data)
Begin
-- Update data
End
End
Else
Begin
-- Insert Row
Begin
[/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Or.... modify just the update part like this.

        UPDATE SCHEDULE_DATA
        SET SCHED_DATE = @SCHED_DATE ,
            SCHEDULED_LINE = @SCHEDULED_LINE ,
            PRIORITY = @PRIORITY ,
            LAST_UPDATE = GETDATE() ,
            COMMENT = @COMMENT ,
            STATUS = @STATUS ,
            REL_DTS = CASE @STATUS
                    WHEN 0 THEN GETDATE()
                    WHEN 4 THEN GETDATE()
                    ELSE null
                   END
            --SCHEDULED_LINE = @SCHEDULED_LINE
        WHERE MYID = @UPDATEID
              [!]And (SCHED_DATE <> @SCHED_DATE 
                   OR SCHEDULED_LINE <> @SCHEDULED_LINE
                   OR PRIORITY <> @PRIORITY
                   OR COMMENT <> @COMMENT
                   OR STATUS <> @STATUS)[/!]

This may fail if the table data contains NULLS because you cannot use the = or <> to compare nulls. But... if you have a constraint on your data the prevents nulls, this should work nicely for you.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jadams0173]

Hey George! I like the second method better. The table does not allow nulls as all fields except the comment are required and I have the comment field set to default to a space. I knew there had to be a better way then what I was about to do.