Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: Cursor required? Or just different UPDATE syntax?

    Skeleton code to update existing records in TableA with earliest staging data from TableB:
    Code:
    UPDATE	TableA
    SET	(Column1, Column2) =
    	(SELECT	COALESCE(TableB.Column1, TableA.Column1),
    		COALESCE(TableB.Column2, TableA.Column2)
    	FROM	TableB
    		INNER JOIN --EarliestRecords
    			(SELECT	KeyColumn,
    				MIN(TimeStamp) AS TimeStamp
    			FROM	TableB
    			GROUP BY KeyColumn) earliestrecords
    			ON TableB.KeyColumn = EarliestRecords.KeyColumn
    			AND TableB.TimeStamp = EarliestRecords.TimeStamp
    	WHERE	TableB.KeyColumn = TableA.KeyColumn);
    Problem is, TOAD indicates that ALL of the records in TableA were updated/affected by the query, regardless of whether there was a matching record in TableB. Since TableA could be hundreds of thousands of records, and TableB will hover around 1500 records, this seems pretty inefficient to me.

    Should I be using a different syntax?
    Should I abandon set-based processing and use a cursor instead, and if so should I run the cursor on TableA or TableB?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Your UPDATE has no WHERE clause, hence it updates all records. It should be something like this:
    Code:
    UPDATE	TableA
    SET	(Column1, Column2) =
    	(SELECT	COALESCE(TableB.Column1, TableA.Column1),
    		COALESCE(TableB.Column2, TableA.Column2)
    	FROM	TableB
    		INNER JOIN --EarliestRecords
    			(SELECT	KeyColumn,
    				MIN(TimeStamp) AS TimeStamp
    			FROM	TableB
    			GROUP BY KeyColumn) earliestrecords
    			ON TableB.KeyColumn = EarliestRecords.KeyColumn
    			AND TableB.TimeStamp = EarliestRecords.TimeStamp
    	WHERE	TableB.KeyColumn = TableA.KeyColumn)
    WHERE EXISTS
    	(SELECT	NULL
    	FROM	TableB
    		INNER JOIN --EarliestRecords
    			(SELECT	KeyColumn,
    				MIN(TimeStamp) AS TimeStamp
    			FROM	TableB
    			GROUP BY KeyColumn) earliestrecords
    			ON TableB.KeyColumn = EarliestRecords.KeyColumn
    			AND TableB.TimeStamp = EarliestRecords.TimeStamp
    	WHERE	TableB.KeyColumn = TableA.KeyColumn);

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I thought of that, but I was trying to avoid another dive into the tables for efficiency sake. But if you recommend this as a good method then I'll use it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Looks like that is working fine. Thanks!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Is the relation between both KeyColumn's of table A and B unique ? that is, one-to-one ?

    One could do this with an update of a join if that's it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •