Update record in a table where the condition depends on other table

Question

I have 2 table in MySQL

• Project:

  
  ProjectID         ProjectStatus
      1                    0
      2                    0
      3                    0
      4                    0
      5                    0
  

• Quotation:

  
  QuotationID         ProjectID            QuotationNumber
      1                    1                     X001
      2                    2                     X002
      3                    3                     X003
      4                    1                     X004
      5                    2                     X005
  

I need a SQL Syntax which can update or edit ProjectStatus in table Project

if the project is in the quotation table then change ProjectStatus to 1 if the project is NOT in the quotation table then change ProjectStatus to 2

So after i runt that SQL syntax, the Project table will become like this:

• Project(After Query Run):

  
  ProjectID         ProjectStatus
      1                    1
      2                    1
      3                    1
      4                    2
      5                    2
  

I tried many queries, but i cant figure it out.

Anyone can help please.

Thank you for your help.

Answer 1

UPDATE Project p
LEFT JOIN Quotation q
ON p.ProjectID = q.ProjectID
SET ProjectStatus = IF(q.ProjectID IS NULL, 2, 1)

Answer 2

UPDATE Project
LEFT OUTER JOIN Quotation
ON Project.ProjectID = Quotation.ProjectID
SET Project.ProjectStatus = IF(Quotation.ProjectID IS NULL, 2, 1)