CODEWITHSUNDEEP
sqldb2sql-updatedb2-luw
Superman
Superman

Reputation: 881

Update only 1 record in DB2 from multiple identical records?

I'm trying to update a single record which is the exact copy of another record. Is there any way to limit or select only 1 record while updating?

Thanks

Upvotes: 0

Views: 1966

Answers (4)

aprogrammer
aprogrammer

Reputation: 1

I have a table with a duplicate rows (possibly more) and just happens I needed something like this. I had issues with your answer as I got error:

"t.myVALUE" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=3.64.106"

but after some tweaking, I arrived at a slight alternative.

UPDATE 
( SELECT t.myVALUE  FROM mytable t 
 WHERE  ( t.mykey = 'duplicatedkeyvalue' ) 
 FETCH FIRST 1 ROW ONLY
) AS z
SET z.myVALUE = 'newvalue';

just seems to need a second alias 'z' for the subselect result

Upvotes: 0

GeekyDaddy
GeekyDaddy

Reputation: 384

Instead of having copies of multiple records which defeats the purpose of a RDBMS. It would be best to create a referencing table to handle a sorting order.

Upvotes: 0

Andrew
Andrew

Reputation: 776

Just curious why exactly you have an exact copy of a record? Do you not have some sort of ID field? Can you show what you've tried?

Really, the best way to avoid this issue must be to have an ID field.

Upvotes: 0

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

You can use FETCH FIRST n ROWS clause .

UPDATE 
( SELECT colA  FROM tableName t WHERE <where condition> FETCH FIRST 1 ROW ONLY
) 
SET t.colA= 'newvalue';

Upvotes: 3

Related Questions