Insert Problem

[masterchef]

Hi All

This is my first posting so I hope I am in the correct area! The following code works perfect

INSERT INTO selected_product_parent_options (NAME) SELECT NAME from parent_options WHERE NAME NOT IN (SELECT NAME from selected_product_parent_options)

how ever if I try this
INSERT INTO selected_product_parent_options (F_ID, NAME) VALUES (2, SELECT NAME from parent_options WHERE NAME NOT IN (SELECT NAME from selected_product_parent_options))

I get errors, As you can see I am comparing two table columns and copying all the entry's in NAME form one table to another, this time around I also want to insert a value into F_ID. I have no idea what I am doing wrong, All and any help would be much appreciated.

[masterchef]

If I try this I get no error but the second value put the select statement in as a text string not the result of the query

INSERT INTO selected_product_parent_options (F_ID, NAME) VALUES ('2', 'SELECT NAME from parent_options WHERE NAME NOT IN (SELECT NAME from selected_product_parent_options)')

[Fumigator]

MySQL may differ from other SQL-based DBMS's but from my SQL experience you can't do a subquery on an INSERT statement and use the VALUES clause-- you either insert an entire row from a subquery, or you insert an entire row from literals (using the VALUES clause).

That said, you should be able to stick the literal in the subquery of the first query you gave us which works perfectly:

INSERT INTO selected_product_parent_options
(F_ID, NAME)
    (SELECT '2', NAME
    FROM parent_options
    WHERE NAME NOT IN
        (SELECT NAME
        FROM selected_product_parent_options)
    )

[masterchef]

I realised I need to extentd the above to get it to work the way I need for my site, I tried this based on the above( which worked great thanks!)

INSERT INTO selected_product_parent_options (F_ID, NAME) (SELECT '{GET.NxT_ID}', NAME FROM parent_options WHERE NAME NOT IN (SELECT NAME FROM selected_product_parent_options AND WHERE F_ID NOT IN (SELECT F_ID FROM selected_product_parent_options))
)"

but I get error

SQL error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND WHERE F_ID NOT IN (SELECT F_ID FROM selected_product_parent_.

the {GET.NXT_ID} is just Interakt code basically a $_GET

Many Thanks for all your help so far.

[masterchef]

DOH!! found it! I just removed the "AND"

INSERT INTO selected_product_parent_options (F_ID, NAME) SELECT '{GET.NxT_ID}', NAME FROM parent_options WHERE NAME NOT IN (SELECT NAME FROM selected_product_parent_options WHERE F_ID NOT IN (SELECT F_ID FROM selected_product_parent_options))

works perfect

[masterchef]

I have extended the above script but I get errors, can anyone see where I have got wrong?

INSERT INTO selected_product_parent_options (F_ID, LK_ID, NAME) 
(SELECT {GET.NxT_ID}, ID FROM parent_options WHERE ID NOT IN (SELECT LK_ID FROM selected_product_parent_options) OR {GET.NxT_ID} NOT IN (SELECT F_ID FROM selected_product_parent_options), NAME FROM parent_options WHERE NAME NOT IN (SELECT NAME FROM selected_product_parent_options) OR {GET.NxT_ID} NOT IN (SELECT F_ID FROM selected_product_parent_options))

error message

SQL error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' NAME FROM parent_options WHERE NAME NOT IN (SELECT NAME FROM se.

[guelphdad]

This is most likely your error:
{GET.NxT_ID}

hard code a value there and see.

[Fumigator]

Break down this "OR" condition and you'll see it makes no sense (are you missing a SELECT in there? Or something?)

OR {GET.NxT_ID} NOT IN (SELECT F_ID
			FROM selected_product_parent_options),

			NAME FROM parent_options
			 WHERE NAME NOT IN (SELECT NAME
					FROM selected_product_parent_options)