Sql or MySql statement produces parse error

Question

I am following this tutorial http://blogs.msdn.com/b/miah/archive/2008/02/17/sql-if-exists-update-else-insert.aspx I'm on the first more strenuous statement at the moment...

I have produced an error and am not sure what near means:

Error: ER_PARSE_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 'IF EXISTS (SELECT * FROM radio WHERE em='someone@gmail.com') UPDATE radio ' at line 1

Here is my code:

//var _={'em':'email@address.com','st':'radio_name'};
var mysqlquery=
"IF EXISTS (SELECT * FROM radio WHERE em='"+_.em+"') "+
    "UPDATE radio SET ("+
                        "em='"+_.em+"',"+
                        "st='"+_.st+"') WHERE em='"+_.em+"' "+
"ELSE "+
    "INSERT INTO radio VALUES ("+
                        "em='"+_.em+"',"+
                        "st='"+_.st+"')";

I would have thought the error came from me using vars and starting/stopping the string but I don't see an obvious mistake so my question is:

Is this valid syntax for mysql or sql? I'm running a mysql database, I would be interested to know what kind of problem this parse error is producing

[MySQL only supports flow control (`IF/ELSE,WHILE,LOOP, etc`)](http://dev.mysql.com/doc/refman/5.0/en/flow-control-statements.html) inside stored procedures or functions. You cannot use them in a regular query. — Michael Berkowski, May 15 '14 at 20:40
If you need to insert or update, use [`INSERT INTO ...ON DUPLICATE KEY UPDATE`](http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html) See also http://stackoverflow.com/questions/4205181/insert-to-table-or-update-if-exists-mysql — Michael Berkowski, May 15 '14 at 20:41
@BENZ.404 The tutorial is MS SQL Server-specific. Every RDBMS' SQL implementation is going to differ, sometimes significantly. The `ON DUPLICATE KEY` is MySQL-specific extension useful for getting around the lack of flow control in regular queries. — Michael Berkowski, May 15 '14 at 20:47
Basics like `SELECT/INSERT/UPDATE` don't usually differ much, but things like updates with joins differ, and logic operations differ or aren't universally supported. — Michael Berkowski, May 15 '14 at 20:50

Gregor · Answer 1 · 2014-05-16T05:45:03.473

0

You have either a wrong tutorial or a wrong database. The tutorial is for mssql and you are running mysql.

In mysql you can use REPLACE for this matter http://dev.mysql.com/doc/refman/5.5/en/replace.html

UPDATE:

You must pass the primary key to replace or the query insert because there will be no duplicates for mysql if you don't. You may also put a unique index to em and then you don't need an extra primary key.

Try something like:

"REPLACE INTO radio 
 VALUES (
      SELECT id,'"+_.st+"','"+_.em+"' FROM radio WHERE em='"+_.em+"'
 )"

Considering id is your primary key.

Important: your select must match the count of your columns so if you have other columns select them just like the id.

edited May 16 '14 at 05:45

answered May 15 '14 at 21:21

Gregor

592
7
20

but replace destroys the row, I need the primary column id to persist but it auto increments with each new row. I need is to update the row if it exists not remove it then copy its content to a new row (this is what replace seams to do) I can't use that – BENZ.404 May 15 '14 at 21:53
It does not if you pass a prmiary key: see update. It'd help if you'd have provided your table structure :) – Gregor May 16 '14 at 05:41

Sql or MySql statement produces parse error

1 Answers1