CODEWITHSUNDEEP
mysqlsql
user2120901
user2120901

Reputation: 3

Mysql update column based on a string from another table

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

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

Upvotes: 0

Views: 282

Answers (2)

rakeshjain
rakeshjain

Reputation: 1761

Did you try this

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

Upvotes: 2

Rijul
Rijul

Reputation: 515

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.

Hope this helps to explain !

Upvotes: 0

Related Questions