Sub Queries in INSERT statement

[TenaciousC]

I am having difficulties understand if a subquery can be used outside of the WHERE clause in an INSERT statement. I am trying to select a value already in the table and insert it into a new row, all in one statement.

My table looks like this:

section_id INT (PK)
section_name VARCHAR
section_order INT
page_id INT

For this, each "page_id" may have many sections, and they are ordered by "section_order" Example:

+------------+--------------+---------------+---------+
| section_id | section_name | section_order | page_id |
+------------+--------------+---------------+---------+
|          1 | sec-one      |             1 |       1 |
|          2 | sec-two      |             2 |       1 |
|          3 | sec-three    |             3 |       1 |
|          4 | sec-one      |             1 |       2 |
|          5 | sec-two      |             2 |       2 |
+------------+--------------+---------------+---------+

As you can see, page_id 1 has three rows associated with it, where page_id 2 has two rows.

The problem I am running into is figuring out if I can get away with one SQL statement instead of two when adding a new section. I want to set the "section_order" field to the next available increment for that page_id.

What my code basically looks like now is this:

$sql = "SELECT 
                  MAX(section_order) + 1  AS section_order
            FROM
                  sections 
            WHERE
                  page_id='$page_id'";

$page_res = mysql_query($sql);
$page_details = mysql_fetch_array($page_res);

$section_order = $page_details['section_order'];

$sql = "INSERT INTO
                  sections
             SET
                  section_name='$section_name',
                  section_order='$section_order',
                  page_id='$page_id.'";
// ...

Now this works, and works fine. However, I am wondering if it's possible to do something like this:

		$sql = "INSERT INTO
					sections
				SET
					section_name='$section_name',
					section_order=(
						SELECT 
                  					MAX(section_order) + 1 AS section_order
           					  FROM
                  					sections 
           					 WHERE
                 					 page_id='$page_id'"),
					page_id='page_id'";

When trying to run this code, I get above statement, I get this error:

"You can't specify target table 'sections' for update in FROM clause"

Here's my end question: Is what I'm trying to do even possible? Or would crawling through the MySQL manual be a waste of my time? (Incidentally, I have already tried to RTFM, but got really, super confused and came here instead).

Thanks in advance.

[Piranha]

First it seems that you have some parsing problem in the code. Look at the colors in your code and you will find it.

I am assuming that you are using MySQL. From the manual:

To avoid ambiguous column reference problems when the SELECT and the INSERT refer to the same table, provide a unique alias for each table used in the SELECT part, and qualify column names in that part with the appropriate alias.

It means that you have to use different aliases in the insert and in the select part of the query. With your example:

        $sql = "INSERT INTO
                    sections s1
                SET
                    section_name='$section_name',
                    section_order=(
                        SELECT
                                      MAX(s2.section_order) + 1 AS section_order
                                 FROM
                                      sections s2
                                WHERE
                                      s2.page_id='$page_id'"),
                    page_id='page_id'"; 

I haven't tried it out.

I would however do it as a "true" insert into ... select query. Something like:

insert into table t1 (t1.column1, t1.column2, t1.column3)
select $section_name, max(t2.column2) + 1, $page_id
from table t2
where t2.column3 = $page_id