mysql insert... not exists statement help!!

[Jiddick ExRex]

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! :)

[ChrisBrightwell]

What is the exact error?

[jeremy.king]

You sure you're using 5.0?

I just executed the following without error



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

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;

[Jiddick ExRex]

What is the exact error?

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.

You sure you're using 5.0?

I just executed the following without error



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

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;


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!

[jeremy.king]

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!

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.

[Jiddick ExRex]

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.

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 "

[jeremy.king]

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() (http://us3.php.net/manual/en/function.mysql-num-rows.php) function.

Good Luck

[Jiddick ExRex]

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() (http://us3.php.net/manual/en/function.mysql-num-rows.php) function.

Good Luck

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