Expert Must see this Update Statment

Question

Post reply

Expert Must see this Update Statment

shashi kant

SSCommitted

Points: 1659
More actions
April 19, 2007 at 3:32 am

#153537

Hi Every one
i face a simple logical problem in update
first i use the following select query for checking the number of records
Select year, month from title where category = 'ENG' and number between 1550 and 1560

and i got the 10 records from this query

but i want to update the year and month for this result
i put this update statement
update title
set year = 1999 , month = 11
where category = 'ENG' and number between 1550 and 1560
i got the result 0 row effected.
apart from the above update statement i use the following where clause
update title
set year = 1999 , month = 11
where category = 'dkhin' and dknumber >=1550 and dknumber <=1560
output : 0 rows effected

i don't know why this happan, try any one , and let me know the exact answer why this update statment not effect the rows of the table...
thanx in advance
shashi kant

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

Are you using a CASE sensitive collation?

update title

set year = 1999 , month = 11

where ucase(category) = ucase('ENG') and number between 1550 and 1560

N 56°04'39.16"
E 12°55'05.25"

shashi kant SSCommitted Points: 1659 More actions · Answer 2

thanx perter larsson

for your valuable solution

i got the result as no of rows get effected using this upper case /lower case

but please describe more about why case sensitive collation occurs..

regards

shashi kant

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

No rows affected? Or you did get some rows affected?

Collation is setup during installation of SQL Server.

You can change collation of a single column too.

N 56°04'39.16"
E 12°55'05.25"

paul_richardson SSCarpal Tunnel Points: 4594 More actions · Answer 4

are there any triggers on the table that may be doing a rollback ?

shashi kant SSCommitted Points: 1659 More actions · Answer 5

no dear

i am not using any trigger in this table.

i got the solution from Ist reply

thanx for reminding me for the trigger

Antares686 SSC Guru Points: 125444 More actions · Answer 6

You are right should not have made any difference if the SELECT returns rows then the UPDATE should have affected them. Can you post your tables DDL so we can see if anything jumps out maybe?

shashi kant SSCommitted Points: 1659 More actions · Answer 7

CREATE TABLE [ReserveStock] (

[DeptMainCode] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[DeptSubCode] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[TitleId] [int] NOT NULL ,

[DateofEntry] [datetime] NOT NULL ,

[COId] [int] NOT NULL ,

[OpeningReserveStock] [int] NULL ,

[Reserve] [int] NULL ,

[UnReserve] [int] NULL ,

[ClosingReserveStock] [int] NULL ,

CONSTRAINT [PK_ReserveStock] PRIMARY KEY NONCLUSTERED

(

[DeptMainCode],

[DeptSubCode],

[TitleId],

[DateofEntry],

[COId]

&nbsp ON [PRIMARY]

) ON [PRIMARY]

GO

paul_richardson SSCarpal Tunnel Points: 4594 More actions · Answer 8

hi,

That isn't the title table. Antares is correct, if the select returned rows they should be updated (unless a trigger disallows it for some reasons). Can you post the correct table ?