Post reply

Help with a query

  • September 13, 2013 at 10:37 am

    #395476

    Hello,

    CREATE TABLE #Tracking ( ID INT IDENTITY(1,1),ProjectName VARCHAR(100),ProductName VARCHAR(20),SNo VARCHAR(600),Haslift BIT,Units INT)

    INSERT INTO #Tracking ( ProjectName ,ProductName ,SNo ,Haslift,Units)

    SELECT 'P1','Prod1','P-01',1,183

    UNION

    SELECT 'P1','Prod1','P-01',1,178

    UNION

    SELECT 'P1','ABC','P-01',1,12

    UNION

    SELECT 'P2','Prod1','P-019',1,14

    UNION

    SELECT 'P2','Prod1','P-019',1,1888

    SELECT * FROM #Tracking

    DROP TABLE #Tracking

    For SNo P-01 we have productname =ABC so I want to keep the Haslift field's to 1 for the corresponding ID 's 1,2 and 3

    For SN0 P-019 we donot have ProductName ABC so I want to update Haslift field to 0 for IDs 4,5

    Thanks,

  • September 13, 2013 at 10:54 am

    #1650117

    Here are two options. Be sure to understand them before using them in production.

    If you have any question, feel free to ask.

    UPDATE t SET

    Haslift = 0

    FROM #Tracking t

    WHERE NOT EXISTS( SELECT 1

    FROM #Tracking x

    WHERE x.ProductName = 'ABC'

    AND t.SNo = x.SNo)

    UPDATE t SET

    Haslift = 0

    FROM #Tracking t

    WHERE t.SNo NOT IN( SELECT x.SNo

    FROM #Tracking x

    WHERE x.ProductName = 'ABC')

    Beware of NOT IN because it has a weird way to work when NULLS are present. Check the following article http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • September 13, 2013 at 11:19 am

    #1650124

    Thanks . It works!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply