Set 1 Column of a MYSQL Query to Null

Question

I have the following MYSQL query which when executed shows me all the invalid Comment_IDs in the table Order_Info that don't exist in the Order_Comments table. How would I modify this query to set the Comment_ID value to NULL for each record found?

   SELECT Order_Info.* 
     FROM Order_Info 
LEFT JOIN Order_Comments ON Order_Info.COMMENT_ID = Order_Comments.Comment_ID
    WHERE Order_Comments.Comment_ID IS NULL 
      AND Order_Info.COMMENT_ID IS NOT NULL

Order_Comments table looks like this:

Comment_ID  Order_Number Order_Comments
14    8989  Submitted by Gordon, Customer Se...
15    4544  Please include the cd when the b...
17    8787  Previously ordered by company, a...
23    8789  We downloaded the trial. Our is q...

Answer 1

My Solution was more of a workaround.

1. Export the query above to a csv file, open in OpenOffice Calc and delete the comment_ID field leaving the header intact
2. Run the query and delete the results from the Order_Info table
3. Import the new csv file created in step 1 and append using NaviCat

So, maybe not the ideal solution, but it worked.

Answer 2

You can write the update statement with a correlated subquery that is used in the where clause

UPDATE Order_Info oi
SET comment_ID = NULL
WHERE NOT EXISTS (
    SELECT *
    FROM Order_Comments oc
    WHERE oc.comment_ID = oi.comment_ID)

In plain English: In OrderInfo, set the commentID to null when there is no matching entry in OrderComments for this rows commentID).

The statement should be standard SQL and not be mySQL specific.