Increment Suffix on duplicate

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

LVL 58
GaryAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
GaryConnect With a Mentor Author Commented:
Error in query (1064): Syntax error near 'from url b' at line 8
0
 
John_VidmarCommented:
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
 
GaryAuthor Commented:
You can't specify target table 'b' for update in FROM clause
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
John_VidmarCommented:
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
 
GaryAuthor Commented:
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
 
SharathData EngineerCommented:
Is it MySQL or SQL Server?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.