Amazing & Confusing Update Query!!!

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

SQL Server Development (2000)

Amazing & Confusing Update Query!!!

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-27 : 10:43:49

Behdad Khoshbin writes "Hello
I have created the following table in sqlserver:
CREATE TABLE [dbo].[Books_Tbl] (
[intBookID_PK] [int] NOT NULL ,
[strBookTitle] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[intAuthorID_FK] [int] NOT NULL ,
[imgPhoto] [image] NULL ,
[intBookSamplePro] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

and I try to run the following query :

declare @intBookID_PK int
set @intBookID_PK=15
declare @strBookTitle nvarchar(50)
set @strBookTitle=N'Test OK3!'
declare @intAuthorID_FK int
set @intAuthorID_FK=1
--declare @imgPhoto image
--set @imgPhoto=null
declare @intBookSamplePro int
set @intBookSamplePro=null

--=========================================================================================================================
declare @Original_intBookID_PK int
set @Original_intBookID_PK=15
declare @Original_strBookTitle nvarchar(50)
set @Original_strBookTitle=N'Test OK3!'
declare @Original_intAuthorID_FK int
set @Original_intAuthorID_FK=1
declare @Original_imgPhoto int
--set @Original_imgPhoto=null
declare @Original_intBookSamplePro int
set @Original_intBookSamplePro=null

UPDATE Books_Tbl SET intBookID_PK = @intBookID_PK, strBookTitle = @strBookTitle, intAuthorID_FK = @intAuthorID_FK,
imgPhoto = null, intBookSamplePro = @intBookSamplePro
WHERE (intBookID_PK = @Original_intBookID_PK) --AND (intAuthorID_FK = @Original_intAuthorID_FK)
AND((intBookSamplePro = @Original_intBookSamplePro) OR (intBookSamplePro IS NULL and @Original_intBookSamplePro is null))

But it affectes no rows!! but commenting out one of @Original_intBookSamplePro is null (OR) intBookSamplePro IS NULL will make it affect one row.
I am using SqlServer 2000
and my table contains 6 rows but one row with following :

[intBookID_PK]=15
[strBookTitle]=Test OK3!
[intAuthorID_FK]=1
null
null

Thanks in advance!
Behdad Khoshbin"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources