SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using case in updating column


Using case in updating column

Author
Message
Michael T2
Michael T2
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 90
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
ScottPletcher
ScottPletcher
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18780 Visits: 7402
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Michael T2
Michael T2
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 90
actually that wont work, if shipweight2 and shipweight3 are both null it will update them both. I dont want that
Carl0s_
Carl0s_
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 91
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


dwain.c
dwain.c
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16977 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
ScottPletcher
ScottPletcher
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18780 Visits: 7402
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Michael T2
Michael T2
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 90
oh right sorry about that.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search