HELP - UPDATE Query & NULL Values

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

General SQL Server Forums

New to SQL Server Programming

HELP - UPDATE Query & NULL Values

Author

Topic

RAVSKINS
Starting Member

21 Posts

Posted - 2009-06-09 : 11:40:46

I'm attempting to build a SQL job that will run the below query on a schedule as needed. However, while testing the SQL, I'm getting the following message in Query Analyzer:

quote:
(0 row(s) affected)

I have verified that there are hundreds of rows that should be updated.

The following is the Query that I'm using:

quote:

UPDATE [BUBBA_GUMP].[dbo].[Request_test]
SET finalized = '1',
Sent_for_approval = '1',
Previous_owner_ID = NULL,
Sent_from_ID = NULL
WHERE (finalized = '0'
OR sent_for_approval = '0')
AND revision_number <> '00'
AND Is_Latest = '0'

I believe that the two bolded "SET" lines are my problem, but I'm unsure as to how to correct the query.

Any help would be greatly appreciated.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-06-09 : 11:48:02

Does this return something?

SELECT * from [BUBBA_GUMP].[dbo].[Request_test]
WHERE (finalized = '0'
OR sent_for_approval = '0')
AND revision_number <> '00'
AND Is_Latest = '0'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-09 : 11:57:26

quote:
Originally posted by RAVSKINS

I'm attempting to build a SQL job that will run the below query on a schedule as needed. However, while testing the SQL, I'm getting the following message in Query Analyzer:

quote:
(0 row(s) affected)

this is not an error but just indication of number of rows affected by operation
I have verified that there are hundreds of rows that should be updated.

The following is the Query that I'm using:

quote:

UPDATE [BUBBA_GUMP].[dbo].[Request_test]
SET finalized = '1',
Sent_for_approval = '1',
Previous_owner_ID = NULL,
Sent_from_ID = NULL
WHERE (finalized = '0'
OR sent_for_approval = '0')
AND revision_number <> '00'
AND Is_Latest = '0'

are you sure there are records satisfying below criteria?
WHERE (finalized = '0'
OR sent_for_approval = '0')
AND revision_number <> '00'
AND Is_Latest = '0'
I believe that the two bolded "SET" lines are my problem, but I'm unsure as to how to correct the query.

Any help would be greatly appreciated.

RAVSKINS
Starting Member

21 Posts

Posted - 2009-06-09 : 11:58:21

quote:
Originally posted by vijayisonly

Does this return something?

SELECT * from [BUBBA_GUMP].[dbo].[Request_test]
WHERE (finalized = '0'
OR sent_for_approval = '0')
AND revision_number <> '00'
AND Is_Latest = '0'

Yes - 238 rows.

I was able to update several rows by omitting the "Previous_owner_ID = NULL" & "Sent_from_ID = NULL" lines from my SET clause, but it reverts back to "0 rows affected" when I include them.

RAVSKINS
Starting Member

21 Posts

Posted - 2009-06-09 : 12:10:15

quote:
Originally posted by visakh16

quote:
Originally posted by RAVSKINS

I'm attempting to build a SQL job that will run the below query on a schedule as needed. However, while testing the SQL, I'm getting the following message in Query Analyzer:

quote:
(0 row(s) affected)

this is not an error but just indication of number of rows affected by operation
I have verified that there are hundreds of rows that should be updated.

The following is the Query that I'm using:

quote:

UPDATE [BUBBA_GUMP].[dbo].[Request_test]
SET finalized = '1',
Sent_for_approval = '1',
Previous_owner_ID = NULL,
Sent_from_ID = NULL
WHERE (finalized = '0'
OR sent_for_approval = '0')
AND revision_number <> '00'
AND Is_Latest = '0'

are you sure there are records satisfying below criteria?
WHERE (finalized = '0'
OR sent_for_approval = '0')
AND revision_number <> '00'
AND Is_Latest = '0'
I believe that the two bolded "SET" lines are my problem, but I'm unsure as to how to correct the query.

Any help would be greatly appreciated.

Yes, see above.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-09 : 12:20:29

are columns Previous_owner_ID & Sent_from_ID not nullable? what does below return?

SELECT IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='Request_test' AND COLUMN_NAME IN ('Previous_owner_ID','Sent_from_ID')

RAVSKINS
Starting Member

21 Posts

Posted - 2009-06-09 : 12:23:25

quote:
Originally posted by visakh16

are columns Previous_owner_ID & Sent_from_ID not nullable? what does below return?
SELECT IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='Request_test' AND COLUMN_NAME IN ('Previous_owner_ID','Sent_from_ID')

YES & YES

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-09 : 12:26:19

is there any update trigger on [BUBBA_GUMP].[dbo].[Request_test] table?

RAVSKINS
Starting Member

21 Posts

Posted - 2009-06-09 : 13:14:19

quote:
Originally posted by visakh16

is there any update trigger on [BUBBA_GUMP].[dbo].[Request_test] table?

Not exactly sure what that is or how to check.?.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-09 : 13:19:33

are you using sql 2005?

RAVSKINS
Starting Member

21 Posts

Posted - 2009-06-09 : 13:25:53

2000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-09 : 13:34:20

[code]
select a.name 'Table', u.name 'Update Trigger'
from sysObjects a
inner join sysObjects u
on a.updtrig = u.id
where a.type != 'TR'
and a.name = 'Request_test'
[/code]

RAVSKINS
Starting Member

21 Posts

Posted - 2009-06-09 : 13:43:32

Ahhhhh, I'm an idiot.

Disregard.

I had omitted the "AND IS_Latest = '0'" from my WHERE clause, which made it appear that there were additional records that needed updating.

Thank you for your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-09 : 13:47:35

welcome..glad that you spotted it out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources