Mysql On Duplicate Key Update Query Error

Question

I have the following Mysql Query that I am trying to execute using C#.

I tried 2 methods.

Method 1

"INSERT INTO gpsRecent
                    (set_date, set_time, type, asset_id, asset_name, asset_type, gps_lat, gps_lon,             
                     gps_knots, gps_mph, gps_kph, gps_heading, gps_alert, gps_time, set_timestamp, 
                     gps_poll, ign_indicate, batt_charge, batt_level, client_id, group_id,       
                     message_read) 
                    VALUES(" + gps_setDate + ", " + gps_setTime + ", " + gps_type + ", " +   
                    gps_assetId + ", " + gps_assetName + ", " + gps_assetType + ", "
                    + gps_lat + ", " + gps_lon + ", " + gps_knots + ", " + gps_mph + ", " + gps_kph 
                    + ", " + gps_heading + ", " + Convert.ToBoolean(gps_alert) + ", " + Convert.ToString(gps_time) + ", " + gps_setTimestamp + ", " 
                             + Convert.ToBoolean(gps_poll) + ", " + Convert.ToBoolean(gps_ignIndicate) + ", " + Convert.ToBoolean(gps_battCharge) + ", " 
                             + Convert.ToInt16(gps_battLevel) + ", " + gps_clientId + ", " + gps_groupId + ", " + gps_messRead + @")
                    ON DUPLICATE KEY 
                    UPDATE  
                    set_date = VALUES(set_date), set_time = VALUES(set_time), type = VALUES(type), asset_id = VALUES(asset_id), asset_name = VALUES(asset_name), 
                    asset_type = VALUES(asset_type), gps_lat = VALUES(gps_lat), gps_lon = VALUES(gps_lon), gps_knots = VALUES(gps_knots), gps_mph = VALUES(gps_mph), 
                    gps_kph = VALUES(gps_kph), gps_heading = VALUES(gps_heading), gps_alert = VALUES(gps_alert), gps_time = VALUES(gps_time), set_timestamp = VALUES(set_timestamp), 
                    gps_poll = VALUES(gps_poll), ign_indicate = VALUES(ign_indicate), batt_charge = VALUES(batt_charge), batt_level = VALUES(batt_level), client_id = VALUES(client_id),
                    group_id = VALUES(group_id), message_read = VALUES(message_read)
                    WHERE asset_id = '" + gps_assetId + "' AND client_id = '" + gps_clientId + "';";

Method 2

      "INSERT INTO gpsRecent
                (set_date, set_time, type, asset_id, asset_name, asset_type, gps_lat, gps_lon, gps_knots, gps_mph, gps_kph, gps_heading, 
                gps_alert, gps_time, set_timestamp, gps_poll, ign_indicate, batt_charge, batt_level, client_id, group_id, message_read) 
                VALUES(@set_date, @set_time, @type, @asset_id, @asset_name, @asset_type, @gps_lat, @gps_lon, @gps_knots, @gps_mph, @gps_kph, @gps_heading, 
                @gps_alert, @gps_time, @set_timestamp, @gps_poll, @ign_indicate, @batt_charge, @batt_level, @client_id, @group_id, @message_read)
                ON DUPLICATE KEY 
                UPDATE  
                set_date = VALUES(set_date), set_time = VALUES(set_time), type = VALUES(type), asset_id = VALUES(asset_id), asset_name = VALUES(asset_name), 
                asset_type = VALUES(asset_type), gps_lat = VALUES(gps_lat), gps_lon = VALUES(gps_lon), gps_knots = VALUES(gps_knots), gps_mph = VALUES(gps_mph), 
                gps_kph = VALUES(gps_kph), gps_heading = VALUES(gps_heading), gps_alert = VALUES(gps_alert), gps_time = VALUES(gps_time), set_timestamp = VALUES(set_timestamp), 
                gps_poll = VALUES(gps_poll), ign_indicate = VALUES(ign_indicate), batt_charge = VALUES(batt_charge), batt_level = VALUES(batt_level), client_id = VALUES(client_id),
                group_id = VALUES(group_id), message_read = VALUES(message_read)
                WHERE asset_id = '" + gps_assetId + "' AND client_id = '" + gps_clientId + "';";

         MySqlCommand comm_gpsRecent = new MySqlCommand(Entry_gpsRecent, Con_gpsRecent);
        //comm_gpsRecent.Prepare();  
        //inserting each element from GPS split message into its repective table and column in the Track24 database
        comm_gpsRecent.Parameters.AddWithValue("@set_date", gps_setDate);
        comm_gpsRecent.Parameters.AddWithValue("@set_time", gps_setTime);
        comm_gpsRecent.Parameters.AddWithValue("@type", gps_type);
        comm_gpsRecent.Parameters.AddWithValue("@asset_id", gps_assetId);
        comm_gpsRecent.Parameters.AddWithValue("@asset_name", gps_assetName);
        comm_gpsRecent.Parameters.AddWithValue("@asset_type", gps_assetType);
        comm_gpsRecent.Parameters.AddWithValue("@gps_lat", gps_lat);
        comm_gpsRecent.Parameters.AddWithValue("@gps_lon", gps_lon);
        comm_gpsRecent.Parameters.AddWithValue("@gps_knots", gps_knots);
        comm_gpsRecent.Parameters.AddWithValue("?@gps_mph", gps_mph);
        comm_gpsRecent.Parameters.AddWithValue("@gps_kph", gps_kph);
        comm_gpsRecent.Parameters.AddWithValue("@gps_heading", gps_heading);
        comm_gpsRecent.Parameters.AddWithValue("@gps_alert", Convert.ToBoolean(gps_alert));
        comm_gpsRecent.Parameters.AddWithValue("@gps_time", Convert.ToString(gps_time));
        comm_gpsRecent.Parameters.AddWithValue("@set_timestamp", gps_setTimestamp);
        comm_gpsRecent.Parameters.AddWithValue("@gps_poll", Convert.ToBoolean(gps_poll));
        comm_gpsRecent.Parameters.AddWithValue("@ign_indicate", Convert.ToBoolean(gps_ignIndicate));
        comm_gpsRecent.Parameters.AddWithValue("@batt_charge", Convert.ToBoolean(gps_battCharge));
        comm_gpsRecent.Parameters.AddWithValue("@batt_level", Convert.ToInt16(gps_battLevel));
        comm_gpsRecent.Parameters.AddWithValue("@client_id", gps_clientId);
        comm_gpsRecent.Parameters.AddWithValue("@group_id", gps_groupId);
        comm_gpsRecent.Parameters.AddWithValue("@message_read", gps_messRead);

comm_gpsRecent.ExecuteNonQuery(); //executing the INSERT Query Above to put data into gpsRecent

But when I try to execute it I get an error saying that MySql syntax is incorrect.

Can someone please advise me what I am doing wrong.

Answer 1

Its probably because the "WHERE" statement at the end. As MySQL specification sais there is no such thing in ON DUPLICATE KEY UPDATE. ON DUPLICATE KEY you can define what is happening for each row thats duplicated, without conditions.

Answer 2

MySQL does not allow the use of WHERE clause with INSERT... ON DUPLICATE KEY... UPDATE.

This question addressed this same problem and the solution provided is the use of an IF(). Check this link (also specified in the accepted answer to that question) for more info on how to use IF() to include the conditions you need to specify.