[FIXED] How to use an UPDATE Query with an INNER JOIN to update fields within a table

Issue

I keep getting a run time error on the following SQL statement:

UPDATE tbl_1 
INNER JOIN tbl_2 ON tbl_1.PersNo = tbl_2.PersNo 
SET tbl_1.Marked = 'N/A' 
WHERE NOT EXISTS (SELECT * FROM tbl_2 WHERE tbl_1.PersNo = tbl_2.PersNo)

I think I may have some syntax backward, I'm looking to update the Table 1 Marked field with "N/A" (string value) when the PersNo does not exist in Table 2.

This all stems from a function with several SQL statments that allow me to Update the Table 1 Marked field with either "Yes", "No", or "N/A". So if there is a simpler way to do this, I'm open to suggestions.

In short, if the PersNo exists in Table 2 and the Type (from Table 2) is "Summary" then update Table 1 Marked field with "Yes", but is the Type (from Table 2) is "Full" then update Table 1 Marked field with "No", and is the PersNo does not exist in Table 2, update Table 1 Marked field with "N/A".

Solution

Your syntax is indeed incorrect for SQL Server - if I understand your last paragraph you just need a conditional case expression. If the following (of course untested) is not correct hopefully it's enough to put you on the right track:

update t1 set t1.Marked =
    case t2.type
      when 'Summary' then 'Yes'
      when 'Full' then 'No'
      else 'N/A'
    end
from tbl_1 t1
left join tbl_2 t2 on t1.PersNo = t2.PersNo;

Answered By - Stu

Answer Checked By - Marilyn (PHPFixing Volunteer)