Solved

Increment Suffix on duplicate

Posted on 2014-03-26
6
249 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

729 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