Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8

Thread: Insert Problem

  1. #1
    New to the CF scene
    Join Date
    Mar 2007
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert Problem

    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.

  • #2
    New to the CF scene
    Join Date
    Mar 2007
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

  • #3
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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:
    Code:
    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)
        )

  • #4
    New to the CF scene
    Join Date
    Mar 2007
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #5
    New to the CF scene
    Join Date
    Mar 2007
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #6
    New to the CF scene
    Join Date
    Mar 2007
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    extending problem

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

    Code:
    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

    Code:
    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.

  • #7
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    This is most likely your error:
    {GET.NxT_ID}

    hard code a value there and see.

  • #8
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Break down this "OR" condition and you'll see it makes no sense (are you missing a SELECT in there? Or something?)
    Code:
    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)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •