UPDATE only one record - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

UPDATE only one record

Author

Topic

Peini
Starting Member

5 Posts

Posted - 2003-11-03 : 11:07:00

I have a table where I need to update the data that stores on AS400. In that table, there's no primary key or unique identfier. So, there're some records totally duplicate.
What I'm trying to do right now is to update a field in the first record although there are more than one duplicated.
So far, whatever I try, I get all the duplicated records updated.
Is there a way in AS400 you can do that?

Thanks,

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-03 : 11:16:13

One option would be to try to concatenate fields to create some kind of uniqueness. -not a good option
Another would be de-duplicating the table records and adding a primary key - highly recommended

Peini
Starting Member

5 Posts

Posted - 2003-11-03 : 13:09:30

Unfortunately, I can't change the structure of the table and
data have to be updated as requested.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-03 : 15:48:22

Use SET ROWCOUNT 1. Take a look at SET ROWCOUNT in BOL.

Tara

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-03 : 17:16:25

Good suggestion Tara,

I believe the DB2 equivalent of "SET ROWCOUNT n" is "Fetch First n Rows Only" clause

Peini
Starting Member

5 Posts

Posted - 2003-11-03 : 17:24:10

Quoted from the DB2 document
'The fetch-first-clause cannot appear in a statment containing an UPDATE clause'
???

I just tried that, looks like db2 doesn't know this command.
SET ROWCOUNT

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-03 : 17:51:46

Another option would be to create an intermediate temp table which does contain an identifier, perform your processing on it and rebuild the base table.


--This would be your base table

	create table #q (value varchar(20))
go
	insert into #q select 'somedup1'
	insert into #q select 'somedup1'
	insert into #q select 'somedup2'
	insert into #q select 'somedup2'

	select * from #q

--Create an intermediate work table which is a copy of the 
--base table with an identity column applied

	create table #i (n int identity(0,1),value varchar(20))

--insert all your base table records into this intermediate table

	insert into #i select value from #q 

	select * from #i

--perform your update logic on intermediate table using identifier

	update #i set value = 'newvalue1' from #i 
	where n in (select top 1 n from #i where value = 'somedup1' order by n asc)

	update #i set value = 'newvalue2' from #i 
	where n in (select top 1 n from #i where value = 'somedup2' order by n asc)

--Place this code in atomic transaction

	delete #q
	insert into #q select value from #i
	
--base table with updates

	select * from #q

	drop table #i
	drop table #q

Peini
Starting Member

5 Posts

Posted - 2003-11-04 : 09:38:03

Combining with rrn() and min() can do that.

Thank you so much for trying help.

I have the example here, so no matter how many records returned, it will always update the first one.

update test set field1 = 'B'
where rrn(test) in
(select min(rrn(test)) from test where field1 = 'A')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources