Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using case in updating column Expand / Collapse
Author
Message
Posted Friday, October 5, 2012 3:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 15, 2013 10:51 AM
Points: 22, Visits: 84
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
Post #1369333
Posted Friday, October 5, 2012 4:12 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:14 PM
Points: 2,098, Visits: 3,155

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)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
Post #1369339
Posted Friday, October 5, 2012 4:45 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 15, 2013 10:51 AM
Points: 22, Visits: 84
actually that wont work, if shipweight2 and shipweight3 are both null it will update them both. I dont want that
Post #1369345
Posted Friday, October 5, 2012 8:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 8, 2013 9:54 AM
Points: 13, Visits: 89
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]
SET shipweight3 = @weight
WHERE iD = @iD
END
END

END

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

Post #1369363
Posted Sunday, October 7, 2012 7:59 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 3:25 AM
Points: 3,417, Visits: 5,328
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1369592
Posted Monday, October 8, 2012 7:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:14 PM
Points: 2,098, Visits: 3,155
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)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
Post #1369824
Posted Tuesday, October 9, 2012 12:00 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 15, 2013 10:51 AM
Points: 22, Visits: 84
oh right sorry about that.
Post #1370519
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse