Update duplicate value in MySql with nested select

Question

Please help me with my MySql Script, I caught up with an error. I have a Table (tbl_products) with the field of p_id and prod_skuid i wanted to update all duplicate prod_skuid into my tbl_products.

Here is my MySql script:

UPDATE tbl_product t1 SET t1.prod_skuid = t.prod_skuid  
(
SELECT p_id, prod_skuid from 
(SELECT p_id,if(@row = prod_skuid, @count, @count := 1),
 CONCAT(prod_skuid, '', @count) as prod_skuid,
 @row := prod_skuid,
 @count := @count + 1
 from tbl_product JOIN (SELECT @count := 0, @row := "") as t
 WHERE prod_skuid IN(SELECT prod_skuid from tbl_product group by prod_skuid having count(prod_skuid) > 1)
) as final                          
) as t  
WHERE t1.p_id = t.p_id

I managed to display and changed the prod_skuid just using the nested select without the update scrpit, however when i put the update script it says there is an error with my code:

SELECT p_id, prod_skuid from 
(SELECT p_id,if(@row = prod_skuid, @count, @count := 1),
 CONCAT(prod_skuid, '', @count) as prod_skuid,
 @row := prod_skuid,
 @count := @count + 1
 from tbl_product JOIN (SELECT @count := 0, @row := "") as t
 WHERE prod_skuid IN(SELECT prod_skuid from tbl_product group by prod_skuid having count(prod_skuid) > 1)
) as final

Error: Mysql Error Message

score 0 · Accepted Answer · answered May 11 '18 at 04:07

0

Anyways, I got now the answer. I`ve run so many test and now here it is:

UPDATE tbl_product t1, 
( SELECT p_id as unique_id, new_name FROM
( SELECT p_id, IF(@ROW = prod_skuid, @COUNT, @COUNT := 1), 
CONCAT(prod_skuid, ' - ', @COUNT) AS new_name, 
@ROW := prod_skuid, 
@COUNT := @COUNT + 1 
FROM tbl_product JOIN (SELECT @COUNT := 0, @ROW := "") AS t 
WHERE prod_skuid IN(SELECT prod_skuid FROM tbl_product 
GROUP BY prod_skuid HAVING COUNT(prod_skuid) > 1) ) AS temp_test ) 
as testing SET t1.prod_skuid = testing.new_name where t1.p_id = testing.unique_id

answered May 11 '18 at 04:07

Leonard

19
9

There's no ORDER BY clause here, which seems like a strange omission – Strawberry May 11 '18 at 07:05
There`s no need to use order by clause in that query. – Leonard May 18 '18 at 10:09
Plainly, that's not true, but whatever. – Strawberry May 18 '18 at 12:42
Actually, I am using that code. And it gets what I wanted to query. – Leonard May 19 '18 at 12:05
Yes - but in the same way that a broken clock is right twice a day. – Strawberry May 19 '18 at 12:45

Update duplicate value in MySql with nested select

1 Answers1