MySql How to edit all duplicate mysql values

Databases
1

Hi i was wondering if someone could give me some insight or links that might help.

I have the following in my database:

mysql> select URL, ShortCode from stock where Name like 'Rex%';
+--------------------------------------------------------------------------------------+-----------+
| URL                                                                                  | ShortCode |
+--------------------------------------------------------------------------------------+-----------+
| http://www.moneyweb.co.za/moneyweb-click-a-company/Rex-Trueform-Clothing-Company-Ltd | RTN       |
| http://www.moneyweb.co.za/moneyweb-click-a-company/Rex-Trueform-Clothing-Company-Ltd | RTO       |
| http://www.moneyweb.co.za/moneyweb-click-a-company/Rex-Trueform-Clothing-Company-Ltd | RTOP      |
+--------------------------------------------------------------------------------------+-----------+

As you can see the URL’s is all the same, but the ShortCode’s is different. Is there a way how i can append all duplicate URL’s like

example:

mysql> select URL, ShortCode from stock where Name like 'Rex%';
+--------------------------------------------------------------------------------------+-----------+
| URL                                                                                  | ShortCode |
+--------------------------------------------------------------------------------------+-----------+
| http://www.moneyweb.co.za/moneyweb-click-a-company/Rex-Trueform-Clothing-Company-Ltd | RTN       |
| http://www.moneyweb.co.za/moneyweb-click-a-company/Rex-Trueform-Clothing-Company-Ltd-2 | RTO       |
| http://www.moneyweb.co.za/moneyweb-click-a-company/Rex-Trueform-Clothing-Company-Ltd-3 | RTOP      |
+--------------------------------------------------------------------------------------+-----------+

So if i where to connect with

http://www.moneyweb.co.za/moneyweb-click-a-company/Rex-Trueform-Clothing-Company-Ltd-3

, then the correct page must display with the correct coresponding ShortCode.

Any way how to automate this. I have about 500 links to check and it looks like its gonna be a long day at the office

2

Do you have any unique ID column in that table?
If yes, then you can append it to the duplicated URL’s.

How i would do that:

  1. Create new temporary table, say “stock_dups” with fields URL (same type as original), DUPS (int)

  2. Fill that table:

    INSERT INTO stock_dups (url, dups)
    SELECT url, COUNT(url) FROM stock GROUP BY url

Now that table contains pairs “URL - number of duplicates”
3. Update URLs in original table, attaching ID to every URL with more than one duplicate:

UPDATE stock s SET url = CONCAT(url, '-', id) 
WHERE url IN (SELECT url FROM stock_dups WHERE dups > 1)

Remove table “stock_dups”.
That’s it.

3

[quote=“wayne_links, post:1, topic:112708, full:true”] Is there a way how i can append all duplicate URL’s like …[/quote]i’m having trouble understanding why you would want to do this

by appending some string to the end of the url, you are creating a different url, one that probably doesn’t actually work

what problem do the duplicate urls cause you? can you explain the difficulty?

4

Just visit URL examples he is provided:
http://www.moneyweb.co.za/moneyweb-click-a-company/rex-trueform-clothing-company-ltd-2
http://www.moneyweb.co.za/moneyweb-click-a-company/rex-trueform-clothing-company-ltd-3

there is two different pages

5

thank you, i should have done that, it explains the problem

so appending an id isn’t going to work, it actually has to be the numbers -2, -3, etc. for each dupe

6

i guess no matter what exactly will be appended (id or just counter)
the main goal is get different URLs for different pages
but only topic starter knows best =)

7

Thats correct, an id does not seem to work it actually has to be the numbers -2, -3, etc. for each dupe

8

Im thinking maybe i should extract all duplicate URL’s to a php array, then with a foreach append the numbers -2, -3, etc. for each dupe.

9

if you can’t use IDs then yes, your solution is simple PHP script or MySQL stored procedure.
I can help you with PHP if you want

but can you explain why IDs is bad? is it only aesthetic reason? i mean most of sites uses them in urls, even this forum

10

[quote=“megazoid, post:9, topic:112708, full:true”] i mean most of sites uses them in urls, even this forum
[/quote]

great point!

11

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.