How to use an UPDATE Query with an INNER JOIN to update fields within a table-CodePudding

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".

CodePudding user response：

Part of the issue is SQL Server likes you to use table aliases and a FROM clause when using a JOIN with an UPDATE, and the SET comes before the tables:

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

This isn't exactly ansi standard for UPDATES with JOINs, but I like it because it makes it easy to write a SELECT query first and then quickly translate it into an UPDATE.

But now we've got this far the logic seems wrong. If the NOT EXISTS() succeeds, the JOIN condition won't match any rows, so nothing will change. For this part, you need to think more on what you're trying to do. Based on the written description, I think you want this:

UPDATE t1
SET t1.Marked = CASE WHEN t2.Type='Summary' THEN 'Yes'
                        WHEN t2.Type='Full' THEN 'No'
                        ELSE 'N/A' END 
FROM tbl_1 t1
LEFT JOIN tbl_2 t2 ON t1.PersNo = t2.PersNo 
    AND t2.Type IN ('Summary', 'Full')

This all stems from a [procecdure] 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.

I'd bet good money it's possible to consolidate all or most of those steps into a single query that will perform the same job in a small fraction of the time, but to help with that we'd have to see the whole procedure.

CodePudding user response：

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;