c# - 对 MySQL 表进行排序，然后更新行

Question

我想对表进行排序 http://sqlfiddle.com/#!2/87fa6按 jobs.datetime 升序排列，然后仅选择 jobs.operator_id=0 和 jobs.status=open 的行。

一旦表具有此结构，我想设置 jobs.operator_id=bidList[i].OperatorId 但这应该只发生在 booking.plot_id=bid.plot_id< 的行中.

我已经购买了以下 2 个 SQL 语句 - 是否可以以某种方式将它们组合起来以满足上述要求？

// Allocate jobs to bids
for (var i = 0; i < bidList.Count; i++)
{

    // Select rows where booking.status=open, booking.postcode=_plot and
    // booking.operator_id=0. 
    // Once this is done, order by datetime

    query = "SELECT operator_id, plot_id, status FROM booking " +
            "WHERE status='open' AND postcode='" + _plot + "' AND operator_id=0" +
            "ORDER BY datetime";

    // Whilst maintaining the structure above, set 
    // booking.operator_id=bidList[i].OperatorId, 
    // booking.status=allocated where BidList.PlotId = jobs.plotid

    query += "UPDATE t1 SET t1.operator_id='" +
             bidList[i].OperatorId + "', t1.status='Allocated' " +
             "FROM booking_view T1 "+ "JOIN bid t2 ON t1.plot_id=t2.plot_id";
}           

Answer 1

像这样更改第二个查询:

"UPDATE t1 SET t1.operator_id='" +
bidList[i].OperatorId + "', t1.status='Allocated' " +
"FROM booking_view T1 "+
"JOIN bid t2 ON t1.plot_id=t2.plot_id";