Solved

Increment Suffix on duplicate

Posted on 2014-03-26
6
243 Views
Last Modified: 2014-05-21
If the url exists more than once then subsequent duplicates should have their suffix set to 1,2,3 etc
So starting with...
URL		SUFFIX	ID
/test.html	Null	1
/test.html	Null	2
/test.html	Null	3

Open in new window


I would end up with...
URL		SUFFIX	ID
/test.html	Null	1
/test.html	1	2
/test.html	2	3

Open in new window

0
Comment
Question by:Gary
  • 3
  • 2
6 Comments
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39956711
If you have a newer SQL Server then you could formulate an elegant solution with row_number, however, this solution works on any server:
update	#tablename
set	SUFFIX =
	(	select	count(*)
		from	#tablename	a
		where	a.URL = #tablename.URL
		and	a.ID <= #tablename.ID
	)

Open in new window

0
 
LVL 58

Author Comment

by:Gary
ID: 39956815
You can't specify target table 'b' for update in FROM clause
0
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39957436
If you want to alias the target table with b:
update	b
set	SUFFIX =
	(	select	count(*)
		from	#tablename	a
		where	a.URL = b.URL
		and	a.ID <= b.ID
	)
from	#tablename	b

Open in new window

0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 58

Accepted Solution

by:
Gary earned 0 total points
ID: 39961943
Error in query (1064): Syntax error near 'from url b' at line 8
0
 
LVL 58

Author Comment

by:Gary
ID: 39962375
Currently I am using a variation of your solution, but would prefer it one go.

CREATE TABLE temp_url LIKE url;
INSERT INTO temp_url SELECT * FROM url; update url set suffix=null;
update url set suffix=(select count(*) from temp_url a where a.url = url.url and a.url_id < url.url_id order by url_id);
update url set suffix=null where suffix=0;
DROP TABLE temp_url

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 39992906
Is it MySQL or SQL Server?
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

697 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question