基于另一个表中的字符串的Mysql更新列。

I need to update a column called PrebookCB in a table called Workorders and set it to 1 if the CustomerStatus column in Table Customers equals the string 'Good - Prebook'. I have tried various joins ect and cant seem to get it to work. This seems to be the closest. there would be multiple Workorders for each customer. Workorders have a column called CustomerID that matches the Customers primary index column called CustomerID

我需要在一个名为Workorders的表中更新一个名为PrebookCB的列，并将其设置为1，如果表客户中的CustomerStatus列等于字符串'Good - Prebook'。我试过各种各样的方法，但似乎都无法奏效。这似乎是最接近的。每个客户将有多个工作订单。Workorders有一个名为CustomerID的列，它与名为CustomerID的客户主索引列相匹配。

UPDATE Workorders
       JOIN Customers
       ON Workorders.CustomerID = Customers.CustomerID
SET    Workorders.PrebookCB = 1
WHERE  Customers.CustomerStatus = 'Good - Prebook'

2 个解决方案

#1

Try this,

试试这个,

Update Workorders
set prebookCB = 1
where CustomerID in (select customerid from customers 
where customeerstatus='Good-Prebook')

In this, the UPDATE works on you desired table. WHERE filters the records to update by comparing the customerID to be present in the result of a subquery. The subquery, further filters and select customerID from the customers table only when they have the apt status.

在这个过程中，更新工作在您想要的表上。通过比较子查询结果中的customerID来对记录进行更新。子查询、进一步筛选和从客户表中选择customerID，仅当它们具有apt状态时。

Hope this helps to explain !

希望这有助于解释!

#2

Did you try this

你试试这个

UPDATE Workorders SET PrebookCB = 1 
WHERE CustomerID IN 
    (SELECT CustomerID FROM Customers 
    WHERE CustomerStatus = 'Good - Prebook')

#1