mysql insert... not exists statement help!!

Discussion in 'Mac Programming' started by Jiddick ExRex, Dec 20, 2007.

  1. Jiddick ExRex macrumors 65816

    Jiddick ExRex

    Joined:
    May 14, 2006
    Location:
    Roskilde, DK
    #1
    Ok, this has been driving me nuts since yesterday. All documentation of mysql 5.0 says this should work but my mysql-server simply rejects this statement:

    INSERT INTO Husstand (adresse, stad, postnr) SELECT 'adresse', 'by', 'ptr' FROM dual WHERE not exists (SELECT * FROM Husstand WHERE Husstand.adresse = 'adresse' AND Husstand.stad = 'by' AND Husstand.postnr = 'ptr');

    Basically I want to insert the three values into the 'Husstand' table if those values are not already present. I am given an error on the not exists syntax and don't know what I am doing wrong. Can anyone give me some pointers?

    That would be most helpful! :)
     
  2. ChrisBrightwell macrumors 68020

    ChrisBrightwell

    Joined:
    Apr 5, 2004
    Location:
    Huntsville, AL
  3. jeremy.king macrumors 603

    jeremy.king

    Joined:
    Jul 23, 2002
    Location:
    Fuquay Varina, NC
    #3
    You sure you're using 5.0?

    I just executed the following without error

    Code:
    
    mysql> drop table  if exists Husstand;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> create table Husstand(adresse varchar(16), stad varchar(16),postnr varchar(16));
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> INSERT INTO Husstand (adresse, stad, postnr) SELECT 'adresse', 'by', 'ptr' FROM dual WHERE not exists (SELECT * FROM Husstand WHERE Husstand.adresse = 'adresse' AND Husstand.stad = 'by' AND Husstand.postnr = 'ptr');
    Query OK, 1 row affected (0.00 sec)
    Records: 1  Duplicates: 0  Warnings: 0
    
    
    Here's an alternate query

    Code:
    INSERT INTO Husstand (adresse, stad, postnr) SELECT 'adresse', 'by', 'ptr' FROM dual WHERE (SELECT count(*) FROM Husstand WHERE Husstand.adresse = 'adresse' AND Husstand.stad = 'by' AND Husstand.postnr = 'ptr') = 0;
    
     
  4. Jiddick ExRex thread starter macrumors 65816

    Jiddick ExRex

    Joined:
    May 14, 2006
    Location:
    Roskilde, DK
    #4
    I get an error #1064 which as I understand it is because I am using a reserved word? But I am not. The only thing reserved is 'by' but it's a string and I get the excact same error code even I change it.

    I cannot even run the alternate query.

    Aha. I just found out we're running the 4.1.11 version. How would I go about making the query then? (thx to our host for linking to the 5.0 documentation, utterly confusing me...).

    Thanks a lot for your help!
     
  5. jeremy.king macrumors 603

    jeremy.king

    Joined:
    Jul 23, 2002
    Location:
    Fuquay Varina, NC
    #5
    Have you tried using double quotes instead? Otherwise, is it an option to use a different value?

    P.S. Queries work fine in 4.1.22, but you don't have the luxury to upgrade. Or do you? Try asking your web host.
     
  6. Jiddick ExRex thread starter macrumors 65816

    Jiddick ExRex

    Joined:
    May 14, 2006
    Location:
    Roskilde, DK
    #6
    I tried double quotes and still an error. The value doesn't matter and will be changed as people become members of the website that's using it.

    We don't have the luxury to upgrade and I only need to figure this problem out, when it's done, my job is finished. It's just pretty annoying that it doesn't work when it should you know? :rolleyes:

    This is the whole error btw:

    "#1064 - 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 'WHERE not exists (SELECT * FROM Husstand WHERE Husstand.adresse = "adresse" AND ' at line 1 "
     
  7. jeremy.king macrumors 603

    jeremy.king

    Joined:
    Jul 23, 2002
    Location:
    Fuquay Varina, NC
    #7
    A quick google seems to yeild that 4.1 doesn't support WHERE clauses on a SELECT FROM DUAL statement, which is why it may be choking. Apparently this was fixed in 5.0. Finding documentation on the dual table is shady at best.

    FYI, DUAL isn't an ANSI standard, so saying it SHOULD work is a little misguided.

    Consider changing your query altogether, since you apparently can't upgrade. You could probably get away with an INSERT IGNORE or REPLACE query and don't even bother checking if its already there. I think those statements have a dependance on your table having some sort of key defined.

    Alternatively, you could always query the table before attempting an insert, if you get a row back, then you don't have to INSERT. I don't know what language you are using to interact with the database, however, if it happens to be PHP check out the mysql_num_rows() function.

    Good Luck
     
  8. Jiddick ExRex thread starter macrumors 65816

    Jiddick ExRex

    Joined:
    May 14, 2006
    Location:
    Roskilde, DK
    #8
    Thanks a lot. I learn something new every day I guess :)

    You have been most helpful for me. Over Christmas I will definitely try this out :)
     

Share This Page