Sub-Selection to insert at certain point

Databases
1

Okay I know what i want to do, now i just have to figure out how to do it :stuck_out_tongue:

INSERT INTO quack
SET fileid = ‘a81fe9158791bd510eb17fb4c1604608’,
description = ‘TEST’,
filename = ‘TEST’,
date = ‘2009-11-19’,
chapter = ‘O’,
verse = ( SELECT MAX( verse )
FROM quack
WHERE chapter = ‘O’ ) +1

The idea being, of course, to find the highest value of verse (which is integer type), and add 1 to it to find the correct insert point. MAX(verse) wont work by itself because each chapter has verses 0…n, and so MAX(verse) would pull the highest overall, not just within the chapter.

MySQL (PHPMyAdmin) throws this back at me: #1093 - You can’t specify target table ‘quack’ for update in FROM clause

…i’m not doing an update…

2

This worked perfectly, thanks!

3

I’m not 100% sure, but as far as I know using a select in a normal SELECT query won’t work, but you’d have to use the INSERT … SELECT statement (see here)

For your example this would work out as follows:

INSERT INTO quack
  ( fileid
  , description
  , filename
  , `date`
  , chapter
  , verse ) 
SELECT
  'a81fe9158791bd510eb17fb4c1604608' AS fileid,
  'TEST' AS description,
  'TEST' AS filename,
  '2009-11-19' AS `date`,
  'O' AS chapter,
  MAX( verse ) + 1 AS verse
FROM
  quack
WHERE
  chapter = 'O'

(not tested)