Home » SQL & PL/SQL » SQL & PL/SQL » Problems in Updating Table (Oracle 10g)
Problems in Updating Table [message #432755] Thu, 26 November 2009 01:35 Go to next message
hammad83
Messages: 36
Registered: June 2008
Location: Pakistan
Member
Hi Guys

I have a table with a column name "NTN" and another column same "Business_Serial". Now what I want is that if there are duplicate NTNs in my table, the "Business_Serial" is incremented by 1. For example, if there are 3 rows with same NTN and their Business_Serial is 9000, then the Business_Serial of the three rows should be 9000, 9001 and 9002. I am writing the following query but what it does that it adds 1 to all the Business_Serial with same NTN i.e. 9001, 9001, 9001. Below is my query.

update gstt01 set business_serial = business_serial+1
where ntn in (select ntn from gstt01 group by ntn having count(ntn) > 1) and flag = 'A';


I know my query is wrong. Please suggest a correct one.

Thanks.
Re: Problems in Updating Table [message #432760 is a reply to message #432755] Thu, 26 November 2009 02:23 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Assuming they have the same business_serial (as in your example):
update gstt01 set business_serial = business_serial+rownum-1
where ntn in (select ntn from gstt01 group by ntn having count(ntn) > 1) and flag = 'A';

Regards
Michel

[Updated on: Thu, 26 November 2009 02:24]

Report message to a moderator

Re: Problems in Updating Table [message #432761 is a reply to message #432755] Thu, 26 November 2009 02:37 Go to previous message
hammad83
Messages: 36
Registered: June 2008
Location: Pakistan
Member
Thanks it worked Smile
Previous Topic: Permission to create views on V$
Next Topic: How to chech whether roles got changed to an user
Goto Forum:
  


Current Time: Mon Sep 26 16:16:54 CDT 2016

Total time taken to generate the page: 0.06235 seconds