October 5, 2012 at 3:35 pm
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
October 5, 2012 at 4:12 pm
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".
October 5, 2012 at 4:45 pm
actually that wont work, if shipweight2 and shipweight3 are both null it will update them both. I dont want that
October 5, 2012 at 8:08 pm
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
October 7, 2012 at 7:59 pm
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 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
October 8, 2012 at 7:58 am
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".
October 9, 2012 at 12:00 pm
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