Help Writing Update Query

Mitch McConnell 41 Reputation points

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 
Update [dbo].[_______Test] Set oliExists = case when Select orderlinenumber 
  from [dbo].[_______Test]
  WHERE orderlinenumber not in (Select orderlineid 
                                from [Li]) else false end
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
10,902 questions
{count} votes

Accepted answer
  1. CosmogHong-MSFT 14,591 Reputation points Microsoft Vendor

    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:

    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Guoxiong 8,121 Reputation points

    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;
    0 comments No comments