Using case in updating column

  • ok i have a table called "weights" that has following columns

    id shipweight1 shipweight2 shipweight3

    Right now i have the following query

    @id int,

    @weight int

    as

    if not exists(select id from weights where id = @id)

    begin

    insert into weights (id,shipweight1)

    values(@id,@weight)

    end

    else if *****

    So if the id does not exist in the table then it adds it and shipweight1

    Now for the else if .

    If the record does exist, i want to check if shipweight2 is null if it is i want to update that field if not i want to update shipweight3.

    If shipweight3 is not null i dont want it to update anything. Does this make since.

    Would i just have to do this

    else

    UPDATE weights

    SET shipweight2 = CASE WHEN shipweight2 is null THEN @weight ELSE shipweight2 END,

    shipweight3 = CASE WHEN shipweight2 is not null and shipweight3 is null THEN @weight else shipweight3

    thanks for the help

  • Very close. I would make some slight adjustments as follows:

    UPDATE dbo.weights

    SET

    shipweight2 = CASE WHEN shipweight2 IS NULL THEN @weight ELSE shipweight2 END,

    --CASE is just to doublecheck -- should always apply because of the WHERE conditions

    shipweight3 = CASE WHEN shipweight2 IS NOT NULL AND shipweight3 IS NULL THEN @weight ELSE shipweight3 END

    WHERE

    id = @id AND

    (shipweight2 IS NULL OR shipweight3 IS NULL)

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • actually that wont work, if shipweight2 and shipweight3 are both null it will update them both. I dont want that

  • Maybe not that you need, but this works

    DECLARE

    @id int,

    @weight int,

    @Field varchar(12),

    @Result int

    -- Test values

    --SET @iD = 3

    --SET @weight = 28

    IF NOT EXISTS(SELECT id FROM [dbo].[weights] WHERE iD = @id)

    BEGIN

    INSERT INTO [dbo].[weights](id,shipweight1)

    VALUES(@id,@weight)

    END

    ELSE BEGIN

    SELECT @Result = shipweight2 FROM [dbo].[weights] WHERE iD = @iD

    SELECT @Field =

    CASE

    WHEN @Result <> 0 THEN 'shipweight3'

    ELSE 'shipweight2'

    END

    IF @Field = 'shipweight2' BEGIN

    UPDATE [dbo].[weights]

    SET shipweight2 = @weight

    WHERE iD = @iD

    END

    ELSE BEGIN

    -- check first if is null, for not overwrite

    SELECT @Result = shipweight3 FROM [dbo].[weights] WHERE iD = @iD

    IF @Result is null BEGIN

    UPDATE [dbo].[weights]

    SETshipweight3 = @weight

    WHERE iD = @iD

    END

    END

    END

    -- SELECT * FROM [dbo].[weights] WHERE iD = @iD

  • Is there some reason you can't or won't use a MERGE for this?

    CREATE TABLE #Weights

    (id INT, shipweight1 DECIMAL(5,2)

    ,shipweight2 DECIMAL(5,2), shipweight3 DECIMAL(5,2))

    INSERT INTO #Weights (id, shipweight1, shipweight2)

    SELECT 1, 1.1, NULL

    UNION ALL SELECT 2, 3.1, 2.3

    SELECT * FROM #Weights

    ;WITH SampleData (id, [weight]) AS (

    SELECT 1, 4.4

    UNION ALL SELECT 2, 5.5

    UNION ALL SELECT 3, 6.6)

    MERGE #Weights t

    USING SampleData s

    ON t.id = s.id

    WHEN MATCHED THEN

    UPDATE SET shipweight3 = CASE WHEN shipweight2 IS NULL THEN NULL ELSE weight END

    ,shipweight2 = CASE WHEN shipweight2 IS NULL THEN [weight] ELSE shipweight2 END

    WHEN NOT MATCHED THEN

    INSERT (id, shipweight1)

    VALUES (s.id, weight);

    SELECT * FROM #Weights

    DROP TABLE #Weights

    Looks a bit cleaner to me.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Michael T2 (10/5/2012)


    actually that wont work, if shipweight2 and shipweight3 are both null it will update them both. I dont want that

    No, if both are NULL, only shipweight2 will UPDATE, because I left your condition on the second CASE that verifies that shipweight2 is not null.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • oh right sorry about that.

Viewing 7 posts - 1 through 6 (of 6 total)

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