Mysql update column based on a string from another table

Question

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'

Zagor23 · Accepted Answer · 2013-08-24 17:03:00Z

2

Did you try this

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

edited Aug 24, 2013 at 17:03

Zagor23

1,96312 silver badges14 bronze badges

answered Aug 24, 2013 at 16:50

rakeshjain

1,76111 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2120901 Over a year ago

For some reason 0 rows are affected but I know that 290 customers have the CustomerStatus of 'Good - Prebook'. What else could be affecting it?

rakeshjain Over a year ago

Can you just run "SELECT CustomerID FROM Customers WHERE CustomerStatus = 'Good - Prebook'" query to check that you are getting those 290 rows?

Rijul · Accepted Answer · 2013-08-24 16:59:14Z

0

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 !

answered Aug 24, 2013 at 16:59

Rijul

5157 silver badges19 bronze badges

5 Comments

Praveen Prasannan Over a year ago

How this answer differ from rakeshjain's answer

user2120901 Over a year ago

Foe some reason 0 rows are affected but I know that 290 customers have the CustomerStatus of 'Good - Prebook'. What else could be affecting it?

Rijul Over a year ago

@PraveenPrasannan , I was typing, when the answer was posted, so did not know.

Rijul Over a year ago

@user2120901 , well I can't directly point out the problem as I don't have the defining structures of your tables. On the other hand, try and confirm the Integrity and equivalence of the customerID attribute. The customerID shoulld also be present equivalently in the workorders table. If you can, give more details of your tables structure.

user2120901 Over a year ago

If I remove the last line, WHERE CustomerStatus ='Good-Prebook', then it updates all 6,000 records so I know the CustomerID is matching.

Collectives™ on Stack Overflow

Mysql update column based on a string from another table

2 Answers 2

2 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related