Case Update but the field changes not the value.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Case Update but the field changes not the value.

Author

Topic

leahsmart
Posting Yak Master

133 Posts

Posted - 2004-02-24 : 04:40:18

Hi Reader.

I am trying to use a case statement to update a table. I have created an example (which does not work) below so you know what I am on about. The only problem is that the field I am updating changes depending on the value of @Column.

I am about to give up and write it all using if statements but if you know the answer please write back asap.

Thanks

Leah


CASE @Column
	WHEN 0 THEN UPDATE eSupplychain.dbo.tblLibrary SET TemplateDate = @Value WHERE LibraryID = @LibraryID
	WHEN 1 THEN UPDATE eSupplychain.dbo.tblLibrary SET SelectionCriteria = @Value WHERE LibraryID = @LibraryID
	WHEN 2 THEN UPDATE eSupplychain.dbo.tblLibrary SET CompareTo = @Value WHERE LibraryID = @LibraryID
	WHEN 3 THEN UPDATE eSupplychain.dbo.tblLibrary SET ComparisonCriteria = @Value WHERE LibraryID = @LibraryID
END

leahsmart
Posting Yak Master

133 Posts

Posted - 2004-02-24 : 04:43:03

I have also tried


UPDATE eSupplychain.dbo.tblLibrary SET 
CASE @Column
	WHEN 0 THEN TemplateDate
	WHEN 1 THEN SelectionCriteria
	WHEN 2 THEN CompareTo
	WHEN 3 THEN ComparisonCriteria
END
 = @Value WHERE LibraryID = @LibraryID

nr
SQLTeam MVY

12543 Posts

Posted - 2004-02-24 : 05:14:04

TemplateDate = case when @Column = 0 then @Value else TemplateDate end ,
SelectionCriteria = case when @Column = 1 then @Value else SelectionCriteria end ,
CompareTo = case when @Column = 2 then @Value else CompareTo end ,
ComparisonCriteria = case when @Column = 3 then @Value else ComparisonCriteria end ,

Put a convert around all the @Value's if they are not the same datatype.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

leahsmart
Posting Yak Master

133 Posts

Posted - 2004-02-24 : 05:19:51

Am I adding this to the end of the code above?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-02-24 : 05:30:56

UPDATE eSupplychain.dbo.tblLibrary
set
TemplateDate = case when @Column = 0 then @Value else TemplateDate end ,
SelectionCriteria = case when @Column = 1 then @Value else SelectionCriteria end ,
CompareTo = case when @Column = 2 then @Value else CompareTo end ,
ComparisonCriteria = case when @Column = 3 then @Value else ComparisonCriteria end
WHERE LibraryID = @LibraryID

But why not use a series of if statements if this is the full statement.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

leahsmart
Posting Yak Master

133 Posts

Posted - 2004-02-24 : 05:35:10

The above is not the full statement I have 13 columns, and have another 2 sets of these to do with different columns. Would it be more effective to use if statements?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-02-24 : 05:45:59

It's your call.
Problem with this is that it will update the column to the same value if it is not the one required - this might cause problems if you try to put on audit trail triggers or use replication.
Don't know where this is coming from but I am alway concerned about the overall design when you pass around parameters to select database objects.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

leahsmart
Posting Yak Master

133 Posts

Posted - 2004-02-24 : 05:49:03

well basically we are converting an old database to the new layout. Some one had previuosly kept lots of data in one field separated by pipes |. I have now taken over this area and redesigned the table to have one field for each piece of data, which makes it a lot easier to work with once I have got over the intial hurdle of converting it.

leahsmart
Posting Yak Master

133 Posts

Posted - 2004-02-24 : 05:56:23

Well I have decided to go for an if statement. I tried your code but I kept getting NULLS.

Thanks for all your help


IF (@Column = 0) UPDATE eSupplychain.dbo.tblLibrary SET TemplateData = @Value WHERE LibraryID = @NewLibraryID
IF (@Column = 1) UPDATE eSupplychain.dbo.tblLibrary SET SelectionCriteria = @Value WHERE LibraryID = @NewLibraryID
IF (@Column = 2) UPDATE eSupplychain.dbo.tblLibrary SET CompareTo = @Value WHERE LibraryID = @NewLibraryID
IF (@Column = 3) UPDATE eSupplychain.dbo.tblLibrary SET ComparisonCriteria = @Value WHERE LibraryID = @NewLibraryID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources