0

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

Superman
  • 871
  • 2
  • 13
  • 31

4 Answers4

3

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';
Mudassir Hasan
  • 28,083
  • 20
  • 99
  • 133
  • Ok this query works exactly the way I want it to work. The only thing which will not let it work is the alias "t". When we reached to the SET clause t.ColA, DB engine don't recognize this t. I'm using DB2 LUW. Thanks anyways. :) – Superman Mar 24 '14 at 07:44
0

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.

Andrew
  • 763
  • 7
  • 21
  • I have everything set-up here. It is an intentional change. And I have to go through this the way I'm going currently :) – Superman Mar 24 '14 at 07:28
  • I can't see any way for your situation to be necessary, could you please clarify? (I don't mean to be rude sorry?) – Andrew Mar 24 '14 at 07:35
  • I have a table in which I have a column "column_order" And I need to add a record in this table which have all the values exactly same. Only things which will differ from the old record is its "column_order" and "name" so that I created an exact copy of that record and now want to update these two columns. :) And don't be sorry. ;) – Superman Mar 24 '14 at 07:42
0

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.

GeekyDaddy
  • 384
  • 2
  • 12
0

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

aprogrammer
  • 1