Mysql重复键更新查询错误
本文关键字:查询 错误 更新 Mysql | 更新日期: 2023-09-27 18:18:08
我有以下Mysql查询,我试图使用c#执行。
我试了两种方法。
方法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 + "';";
方法二
"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 ();//执行上面的INSERT查询将数据放入gpsRecent
但是当我尝试执行它时,我得到一个错误说MySql语法不正确。
谁能告诉我我做错了什么
可能是因为末尾的"WHERE"语句。正如MySQL规范所说,在ON DUPLICATE KEY UPDATE中没有这样的事情。ON DUPLICATE KEY你可以定义重复的每一行会发生什么,没有任何条件
MySQL不允许对INSERT... ON DUPLICATE KEY... UPDATE
使用WHERE
子句
这个问题解决了同样的问题,提供的解决方案是使用IF()
。查看此链接(也在该问题的公认答案中指定)以获取有关如何使用IF()
来包含需要指定的条件的更多信息。