SELECT Mysql - Replacing value in one column based on another column

Question

Here is my table:

ID     |     MainLink
----------------------
1      |     1111
2      |     1111
3      |     2222
4      |     3333
5      |     2222
6      |     4444

I need to be able to select and replace the ID value based on a distinct value from the MainLink Column. The new value for the ID Column will have to be the first ID value found. This is what I would like:

ID     |     MainLink
----------------------
1      |     1111
1      |     1111
3      |     2222
4      |     3333
3      |     2222
6      |     4444

Hope this is clear. Thank you for any help.

Would a distinct result set with an occurrence count suffice instead? As that could be done with a simple GROUP BY on MainLink, a MIN(ID) and a COUNT(*). Duplicate rows seem like a waste. — AeroX
– AeroX, Commented Apr 25, 2014 at 17:16
As you said Yes, ID is a primary key. you can't replace it.You can't have same value twice — underscore
– underscore, Commented Apr 25, 2014 at 17:17

Peter Lang · Accepted Answer · 2014-04-25 17:18:33Z

2

This will return all MainLinks and the lowest value of ID available:

SELECT tmp.ID, t.MainLink
  FROM Table1 t
  JOIN ( SELECT MainLink, MIN(ID) AS ID
           FROM Table1
          GROUP BY MainLink
       ) AS tmp ON ( tmp.MainLink = t.MainLink );

Result (see example on SQLFiddle):

ID  MainLinke
1   1111
1   1111
3   2222
4   3333
3   2222
6   4444

answered Apr 25, 2014 at 17:18

Peter Lang

55.7k29 gold badges153 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Pierre Over a year ago

What is the "t" for before MainLink and after Table1?

Pierre Over a year ago

Following Peter's answer, I tried to select 2 tables in the same query. This is what I came up with: SELECT tmp.ID, t.MainLink, a.ID_Product, a.Name, a.Returned, t.LAXS FROM Stock_Items a,StockAccount t JOIN ( SELECT MainLink, MIN(ID) AS ID FROM StockAccount GROUP BY MainLink ) AS tmp ON ( tmp.MainLink = t.MainLink ); WHERE t.ID=a.ID_Product AND a.Name='' AND a.Returned='' AND t.LAXS='Yes' I get "#2008 - MySQL client ran out of memory". Not sure why since the output should not be that big. Any idea?

Peter Lang Over a year ago

t is a table alias, it allows me to refer to Table1 as t. I'm afraid I cannot help you with your memory problem, try searching online and ask another question (maybe better at dba.stackexchange.com) if you cannot solve it.

Gordon Linoff · Accepted Answer · 2014-04-25 17:19:09Z

1

You can do this using update and join. The key is to calculate the minimum id for each MainLink in a subquery:

update table t join
       (select MainLink, min(id) as minid
        from table t
        group by MainLink
       ) ml
       on t.MainLink = ml.MainLink and t.id > ml.minid
   set t.id = ml.minid;

I do find it strange that you actually want to create duplicates in your table.

answered Apr 25, 2014 at 17:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

SELECT Mysql - Replacing value in one column based on another column

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related