InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 02:03:42 GMT20http://www.sqlservercentral.com/Forums/Topic1369333-391-1.aspxoh right sorry about that.Tue, 09 Oct 2012 12:00:20 GMTMichael T2http://www.sqlservercentral.com/Forums/Topic1369333-391-1.aspx[quote][b]Michael T2 (10/5/2012)[/b][hr]actually that wont work, if shipweight2 and shipweight3 are both null it will update them both. I dont want that[/quote]No, if both are NULL, only shipweight2 will UPDATE, [u]because I left your condition on the second CASE that verifies that shipweight2 is not null[/u].Mon, 08 Oct 2012 07:58:07 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1369333-391-1.aspxIs there some reason you can't or won't use a MERGE for this?[code="sql"]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, NULLUNION ALL SELECT 2, 3.1, 2.3SELECT * 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 tUSING SampleData sON t.id = s.idWHEN 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 ENDWHEN NOT MATCHED THEN INSERT (id, shipweight1) VALUES (s.id, weight); SELECT * FROM #WeightsDROP TABLE #Weights[/code]Looks a bit cleaner to me.Sun, 07 Oct 2012 19:59:25 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1369333-391-1.aspxMaybe not that you need, but this works[code="sql"] 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 ENDEND-- SELECT * FROM [dbo].[weights] WHERE iD = @iD[/code]Fri, 05 Oct 2012 20:08:29 GMTCarl0s_http://www.sqlservercentral.com/Forums/Topic1369333-391-1.aspxactually that wont work, if shipweight2 and shipweight3 are both null it will update them both. I dont want thatFri, 05 Oct 2012 16:45:30 GMTMichael T2http://www.sqlservercentral.com/Forums/Topic1369333-391-1.aspxVery close. I would make some slight adjustments as follows:[code="sql"]UPDATE dbo.weightsSET 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)[/code]Fri, 05 Oct 2012 16:12:22 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1369333-391-1.aspxok i have a table called "weights" that has following columns[code="other"]id shipweight1 shipweight2 shipweight3[/code]Right now i have the following query[code="sql"]@id int,@weight intas if not exists(select id from weights where id = @id) begin insert into weights (id,shipweight1) values(@id,@weight) endelse if *****[/code]So if the id does not exist in the table then it adds it and shipweight1Now 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 [code="sql"]else UPDATE weightsSET 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[/code]thanks for the helpFri, 05 Oct 2012 15:35:30 GMTMichael T2