Solved

Increment Suffix on duplicate

Posted on 2014-03-26
6
241 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 40

Expert Comment

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
two ways encryption with php 3 37
awk and Pythagoras? 5 19
Awk & Pythagoras - applying csv file data to the problem 26 49
How to fix Datetime in MySQL? 4 47
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

792 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