Error in MySQL Query. Check if row exist, update or insert

Question

The following query should check if the row exists, if it does, update, if it doesn't, insert.

String sql = "IF EXISTS (SELECT * FROM 'mocha' WHERE uuid = " + uuid + ")" +
        "BEGIN" +
            "UPDATE 'mocha'" +
            "SET join_message = " + message +
            "WHERE uuid ="  + uuid +
        "END" +
        "ELSE" +
        "BEGIN" +
            "INSERT INTO 'mocha' (uuid, join_message, quit_message)" +
            "VALUES ('"  + uuid + ", " + message + " + "" + "')" +
        "END";

I was hoping to be able to do this in one query instead of two, Here's the entire method:

public void setMessage(UUID uuid, String message) {
    String sql = "IF EXISTS (SELECT * FROM 'mocha' WHERE uuid = " + uuid + ")" +
            "BEGIN" +
                "UPDATE 'mocha'" +
                "SET join_message = " + message +
                "WHERE uuid ="  + uuid +
            "END" +
            "ELSE" +
            "BEGIN" +
                "INSERT INTO 'mocha' (uuid, join_message, quit_message)" +
                "VALUES ('"  + uuid + ", " + message + ", ''" + "')" +
            "END";
    BukkitRunnable r = new BukkitRunnable() {
        @Override
        public void run() {
            System.out.println("Join message was set.");
            try {
                Statement statement = connection.createStatement();
                statement.executeUpdate(sql);
            } catch(SQLException e) {
                e.printStackTrace();
            }
        }
    };

    r.runTaskAsynchronously(instance);
}

However I'm getting the following error:

    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''mocha' WHERE uuid = 0f9f822c-d7e6-4000-ac05-6ebc17b82f1d)BEGINUPDATE 'mocha'SET' at line 1
[10:18:52 WARN]:        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
[10:18:52 WARN]:        at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
[10:18:52 WARN]:        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
[10:18:52 WARN]:        at java.lang.reflect.Constructor.newInstance(Unknown Source)
[10:18:52 WARN]:        at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
[10:18:52 WARN]:        at com.mysql.jdbc.Util.getInstance(Util.java:408)
[10:18:52 WARN]:        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
[10:18:52 WARN]:        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3970)
[10:18:52 WARN]:        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3906)
[10:18:52 WARN]:        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
[10:18:52 WARN]:        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2677)
[10:18:52 WARN]:        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
[10:18:52 WARN]:        at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1540)
[10:18:52 WARN]:        at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2595)
[10:18:52 WARN]:        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1468)
[10:18:52 WARN]:        at me.Latte.Models.MochaModel$2.run(MochaModel.java:68)
[10:18:52 WARN]:        at org.bukkit.craftbukkit.v1_11_R1.scheduler.CraftTask.run(CraftTask.java:71)
[10:18:52 WARN]:        at org.bukkit.craftbukkit.v1_11_R1.scheduler.CraftAsyncTask.run(CraftAsyncTask.java:52)
[10:18:52 WARN]:        at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
[10:18:52 WARN]:        at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
[10:18:52 WARN]:        at java.lang.Thread.run(Unknown Source)

What am I doing wrong?

Other problems aside, your SQL has a bunch of words run together without spaces. Print it out and read it. — khelwood, Jun 04 '17 at 14:33
@khelwood Yeah, I realized that now, and I can't figure out what I'm doing wrong. Any help would be appreciative: http://sqlfiddle.com/#!9/4af8f4/4 — kinx, Jun 04 '17 at 15:15
@kinx You might want to look into `REPLACE INTO ...` or `INSERT INTO ... ON DUPLICATE KEY` — Progman, Jun 04 '17 at 17:33

score 0 · Accepted Answer · answered Jun 04 '17 at 14:33

0

Yeah, you are using single quotes instead of backtique around your table name mocha. So your query

INSERT INTO 'mocha'

should be

INSERT INTO `mocha`

answered Jun 04 '17 at 14:33

Rahul

76,197
13
71
125

Thanks, looks like my entire queue is wrong. What am I doing wrong here? http://sqlfiddle.com/#!9/4af8f4/4 – kinx Jun 04 '17 at 14:49

Error in MySQL Query. Check if row exist, update or insert

1 Answers1