| Problems in Updating Table [message #432755] |
Thu, 26 November 2009 01:35  |
hammad83
Messages: 46
Registered: June 2008
Location: Australia
|
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  |
 |
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
