INSERT INTO after CONCAT data from different columns from different rows

Question

So, I have this MySQL table. Here are the relevant columns:

| raw line             | composed_line | next_line              
|----------------------|---------------|------------------------
|                      |               | When I have a bad day,
| I cry my eyes out.   |               | When I cry my eyes out,
| I get ice cream.     |               | When I get ice cream,    
| Things seem better.  |               | When things seem better,     
| I get out of bed.    |               | When I get out bed,

I have this query, which does what I want it to do - it selects the data from the 'next line' column of the penultimate row and combines it with the data from the 'raw_line' column of the most recent row.

SELECT CONCAT((SELECT `next_line` FROM `lines` ORDER BY id DESC LIMIT 1 OFFSET 1), 
(SELECT `raw_line` FROM `lines` ORDER BY id DESC LIMIT 1))

So the result looks like

When things seem better, I get out of bed.

However, all my attempts to take this result and insert it into a column called 'composed_line' of the most recent row have failed. I have tried using PHP and SQL to do this, none of which work.

I wouldn't need to do this if I could figure out a way to display (in PHP) the whoooole table with the 'next_line' and 'raw_line' concat'd and sorted by ID asc, but my attempts to do that have also been dismal failures, always displaying the 'next_line's together, then the 'raw_lines' together, or some other unwanted crappy result (doublesadface).

The result I would want would look like:

When I have a bad day, I cry my eyes out.
When I cry my eyes out, I get ice cream.
When I get ice cream, things seem better.
When things seem better, I get out of bed.

I am brand new to SQL. Any help would be much appreciated.

Please clarify "most recent row" - do you mean the row with the largest ID? The row most recently accessed (there are arguably two)? The most recently updated row? — Air, Aug 14 '13 at 15:50
Where is the source to 'When I have a bad day' in your table? Also, your concats seem to join 2 different rows of your table... — MiStr, Aug 14 '13 at 15:52
Can we assume there is an ID column with values 1, 2, 3, 4 in the above? — Steve Chambers, Aug 14 '13 at 15:52
The one with the largest ID (i.e. the one that is selected by ORDER BY id DESC LIMIT 1) — sachiko, Aug 14 '13 at 15:54
Sorry, the "when I have a bad day" would the from a row previous to all that. I'll add it now. — sachiko, Aug 14 '13 at 15:56
So, it appears you don't need your `composed_line` result to actually be stored in the table - you just want to display it. Correct? — Air, Aug 14 '13 at 16:04
@AirThomas, I need to display the whole table as per the example at the end. If you can think of a way to do that without creating a composed_line column, amazing! It just seemed like it would be easier to display one composed_line after another rather than all the messy concatting, which I can't get to work. — sachiko, Aug 14 '13 at 16:11
In general, you want to avoid that sort of redundancy within a table. It might be worth briefly familiarizing yourself with the concept of [database normalization](http://stackoverflow.com/questions/246701/what-is-normalisation-or-normalization) (but don't get carried away). Looks like you have a couple answers now that will help you find a "display only" type of solution. — Air, Aug 14 '13 at 16:35

score 2 · Accepted Answer · answered Aug 14 '13 at 16:12

2

Assuming you have an "id" column, you'd be better off using it with a join:

update line a
  join line b on a.id = b.id-1
  set a.composed_line = concat(a.next_line,' ',b.raw_line)
where b.raw_line is not null;

or, to just display it:

select
    concat(a.next_line,' ',b.raw_line)
from
   line a
   join line b on a.id = b.id-1

SQLFiddle here

answered Aug 14 '13 at 16:12

Joe

6,767
1
16
29

Nice i was thinking about a delived table to fix the problem. Nice thinking off you that you actually used two diffent join results that you CONCAT to get the result needed. This sql friddle makes it more clear http://sqlfiddle.com/#!2/a5ee3/39 why this works – Raymond Nijland Aug 14 '13 at 16:28

Steve Chambers · Answer 2 · 2016-01-26T08:48:23.643

1

SELECT CONCAT(nextlines.next_line, rawlines.raw_line) AS line
FROM `lines` rawlines
JOIN `lines` nextlines
ON rawlines.id = (nextlines.id % (SELECT COUNT(*) FROM `lines`)) + 1
ORDER BY rawlines.id ASC

See SQL Fiddle demo.

The only slightly complex bit is the modulus (%) with the number of records so that the last ID in rawlines will join to the first ID from nextlines.

edited Jan 26 '16 at 08:48

answered Aug 14 '13 at 16:15

Steve Chambers

37,270
24
156
208

That doesn't produce the OP's desired result. "When I get out bed," shouldn't match to any row. – Joe Aug 14 '13 at 16:28

INSERT INTO after CONCAT data from different columns from different rows

2 Answers2