c# - 在更新语句中使用左连接 where is null

Question

当在预订表中找不到匹配项时(检查是 plot.plot_id = booking.plot_id)，我正在尝试使用左连接将 plot.jobs 行设置为 0。

但是我不断收到以下错误消息:

An exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll but was not handled in user code Additional information: 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 'FROM plot LEFT JOIN booking ON plot.plot_id = booking.plot_id WHERE plot.plot_id' at line 1.

我在这里遵循这个模板:

UPDATE <Table2>
SET Count = 0 
FROM <Table2>
LEFT JOIN <Table1>
    ON <Table2>.<JoinColumn> = <Table1>.<JoinColumn>
WHERE <Table1>.<JoinColumn> IS NULL

到目前为止，这是我的代码:

query =     "UPDATE plot SET jobs = @jobCount WHERE plot_id = @plotID AND postcode='MK';";
query +=    "UPDATE plot " +
            "SET jobs = 0 " +
            "FROM plot " +
            "LEFT JOIN booking " +
            "ON plot.plot_id = booking.plot_id " +
            "WHERE plot.plot_id IS NULL";
cmd =       new MySqlCommand(query, _connection);

Answer 1

您正在尝试对 MySQL 上的 UPDATE 语句使用 SQL Server 语法。这行不通。

UPDATE-syntax of MySQL是:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

table_reference 是用 JOIN-syntax 定义的.

您的查询应该是这样的:

query +=    "UPDATE plot " +
            "LEFT JOIN booking " +
            "ON plot.plot_id = booking.plot_id " +
            "SET jobs = 0 " +
            "WHERE plot.plot_id IS NULL";

我想知道你的意思是不是:WHERE booking.plot_id IS NULL