Help Writing Update Query

Question

Using MS SQL Server how would I change this select statement into a case update statement?

  I want to turn this select statement 
  Select orderlinenumber 
  from [dbo].[_______Test]
  WHERE orderlinenumber not in (Select orderlineid 
                                from [Li])
 into an update statement where I do 
sql 
Update [dbo].[_______Test] Set oliExists = case when Select orderlinenumber 
  from [dbo].[_______Test]
  WHERE orderlinenumber not in (Select orderlineid 
                                from [Li]) else false end

Answer 1

Hi @Mitch McConnell

There is no need to use subquery to determine whether orderlinenumber exists in another table. Just do UPDATE after LEFT JOIN the two tables will be fine, check this:

UPDATE A
SET A.oliExists = CASE WHEN B.orderlineid IS NULL THEN 'False' ELSE 'True' END
FROM [dbo].[_______Test] AS A LEFT JOIN (SELECT DISTINCT orderlineid FROM [Li]) AS B
  ON A.orderlinenumber = B.orderlineid

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I guess you want to set oliExists to true if its orderlinenumber does not exists in the column orderlineid in the table Li, otherwise set oliExists to false. If that is the case, try this:

UPDATE t
SET t.[oliExists] = 1
FROM [dbo].[_______Test] AS t
WHERE NOT EXISTS (SELECT * FROM [Li] WHERE [orderlineid] = t.[orderlinenumber]);

UPDATE t
SET t.[oliExists] = 0
FROM [dbo].[_______Test] AS t
WHERE EXISTS (SELECT * FROM [Li] WHERE [orderlineid] = t.[orderlinenumber]);

-- Or if you want to use CASE
UPDATE t
SET t.[oliExists] = CASE WHEN NOT EXISTS (SELECT * FROM [dbo].[Li] WHERE [orderlineid] = t.[orderlinenumber]) THEN 1 ELSE 0 END
FROM [dbo].[_______Test] AS t;