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]

Update

 

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

[MadTechie]

Whats the error message ?

[masterchef]

Hi Found the answer

 

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

 

After a bit more digging you can't do a subquery on an INSERT statement and use the VALUES clause

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

 

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.

[MadTechie]

Remove the AND

selected_product_parent_options AND WHERE

[masterchef]

I have now extended the above script but I have run into more errors, can anyone spot my error?

 

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 :

 

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.

 

Many Thanks Madtechie for the answer to the pre error!

[bubblegum.anarchy]

Looks like you are missing a bracket at ), NAME => )), NAME but the query is hard to read without some decent formatting!