CODEWITHSUNDEEP
phpmysqlsql
sachiko
sachiko

Reputation: 51

INSERT INTO after CONCAT data from different columns from different rows

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.

Upvotes: 3

Views: 115

Answers (2)

Steve Chambers
Steve Chambers

Reputation: 39434

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.

Upvotes: 1

Joe
Joe

Reputation: 6827

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

Upvotes: 2

Related Questions