UPDATE using IN operator? - SQL Server Forums

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)

UPDATE using IN operator?

Author

Topic

CactusJuice
Starting Member

46 Posts

Posted - 2005-04-06 : 17:08:02

Am I overlooking something really obvious...I hope? The UPDATE gives the following error. But I can change the statement to a SELECT and it works fine

(DailyID column is data type int).

(4 row(s) affected)
Server: Msg 245, Level 16, State 1, Line 27
Syntax error converting the varchar value '95,96,97,98,' to a column of data type int.


DECLARE @pDailyID varchar(500)
SET @pDailyID = '95,96,97,98' --test values

UPDATE dbo.Daily
SET
	Reviewed = @pReviewed,
	ReviewedBy = @pReviewedBy,
	DateReviewed = @pDateReviewed
--WHERE DailyID IN (SELECT IntValue FROM dbo.CsvToInt(@pDailyID))
WHERE DailyID IN (95,96,97,98)

BTW, the commented line uses a CSV parsing UDF but I get the same error. Error is always on the last pass. In other words, if I decrease the string by one number then 3 rows will be inserted.

Cameron

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-04-06 : 17:17:35

Can you post the code for your CsvToInt function. The problem is with the function.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-04-07 : 02:10:50

edit - realized i posed the same solution to the commented out query line

for the other line, it is not working because dailyID is integer and you're comparing it to a string which is @pDailyID.

the solution to your problem is the commented out query

post the function you used, or you can search for fnSplitText (i think that's what they call it)...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources