...

View Full Version : Insert Problem



masterchef
03-26-2007, 03:27 PM
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
03-26-2007, 04:04 PM
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
03-26-2007, 04:58 PM
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
03-26-2007, 06:58 PM
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
03-26-2007, 07:35 PM
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
04-25-2007, 11:46 AM
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
04-25-2007, 03:28 PM
This is most likely your error:
{GET.NxT_ID}

hard code a value there and see.

Fumigator
04-25-2007, 09:37 PM
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)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum