Append string to a record's specified column after SELECT command

Question

I have an sql command that returns me a list of duplicated items (in my MySQL database), only two columns, one for the duplicated value and one for the count of duplicated records.

SELECT title, COUNT(*) c FROM posts GROUP BY title HAVING c > 1

title         c  
---------------
title_1       2 
title_a       2
title_b       2

I assume one result looks like this:(and it's an array of arrays)

objId   title
------------
1    title_1 
2    title_1

So my goal is to append a string to the second item of a result in the array of the duplicated record's like this:

objId   title
------------
1    title_1 
2    title_1_2

I've found a solution to update the record, but I don't have an idea how could I loop through the results that I get after the first sql command so I can't utilize it in practice.

UPDATE posts SET title = CONCAT(IFNULL(title,''), ' 2');

In pseudo code I would do something like this to create the new string for the title:

result[1].title = (oldTitleString," 2");

save result[1];

I'm new in sql and don't really know about the possibilities, maybe there would be an easier way to do it, so I would really appreciate if somebody could show me how can I get the second record from the duplicated item and extend it with another string.

Somlyai Dezső · Accepted Answer · 2016-01-26 20:15:31Z

1

My solution:

SELECT `objId`,`title`, 
(SELECT CONCAT(`title`, '_', `po`.`objId`) 
FROM `posts` `p` 
WHERE `title` = `po`.`title` && `p`.`objId` < `po`.`objId` LIMIT 1) AS `title_custom` 
FROM `posts` `po`

answered Jan 26, 2016 at 20:15

Somlyai Dezső

111 bronze badge

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

Comments

Piotr Siekierski · Accepted Answer · 2016-01-25 22:43:03Z

0

Here is sample fiddle:

http://sqlfiddle.com/#!9/a4164/8

Query looks like this:

select id, title, 
concat(title,'_',
       (select count(*) from posts p2 where p2.title = p1.title and p2.id <= p1.id)),
title,
count(*)  c
from posts p1
group by title
having c > 1

answered Jan 25, 2016 at 22:43

Piotr Siekierski

4942 silver badges7 bronze badges

Collectives™ on Stack Overflow

Append string to a record's specified column after SELECT command

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related