0

I have this query, which selects data from one of the previous row's columns ('next_line'), and data from a different column in the most recent row ('raw_line').

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))

This query works perfectly as it is. But what I want to do is put this output into a different column in the most recent row *('composed_line')*. But when I do this:

UPDATE `lines` set `composed_line`=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)) ORDER BY id DESC LIMIT 1;

I get this error:

#1093 - You can't specify target table 'lines' for update in FROM clause

So I tried this:

UPDATE `lines` set `composed_line`=CONCAT((SELECT `next_line` FROM (SELECT * FROM `lines` ORDER 
BY id DESC LIMIT 1 OFFSET 1) AS `alias`, (SELECT `raw_line` FROM (SELECT * FROM `lines` ORDER BY 
id DESC LIMIT 1)) AS `alias2` ORDER BY id DESC LIMIT 1

And I got this error:

 #1248 - Every derived table must have its own alias

I can't see what I am doing wrong - do 'alias' and 'alias2' not count as derived table aliases?

Any help is so much appreciated!

Renjith
  • 5,783
  • 9
  • 31
  • 42
sachiko
  • 51
  • 8
  • possible duplicate of [every derived table must have its own alias](http://stackoverflow.com/questions/1888779/every-derived-table-must-have-its-own-alias) – hkf Aug 13 '13 at 05:54

1 Answers1

0

Your inner select needs an alias also:

UPDATE `lines` set `composed_line`= 
CONCAT((SELECT `next_line` FROM (SELECT * FROM `lines` ORDER 
BY id DESC LIMIT 1 OFFSET 1) AS `alias`, 
(SELECT `raw_line` FROM (SELECT * FROM `lines` ORDER BY 
id DESC LIMIT 1) AS `alias3` ) AS `alias2` ORDER BY id DESC LIMIT 1

See also: every derived table must have its own alias

Community
  • 1
  • 1
hkf
  • 4,440
  • 1
  • 30
  • 44
  • It was missing )) from the end. However, this does not actually do what I want it to do. It just fills all the rows' 'composed_line' with the 'next_line' of the previous row. :( – sachiko Aug 13 '13 at 06:21