Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Increment Suffix on duplicate

Posted on 2014-03-26
6
Medium Priority
?
257 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

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. …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

618 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