make a job that updates data by row amount

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

make a job that updates data by row amount

Author

Topic

quincy
Starting Member

6 Posts

Posted - 2007-08-06 : 16:33:15

I need to make a job that will update up to 8000 rows with the list description of 'berkhold' to 'berknew' in SQL 2000. This is something that I have to do with several projects manually every day by doing the following 2 steps.

SELECT ListDescription, CRRecordID
FROM dbo_BerkleyGroupInventory
WHERE ListDescription ="BerkHold" AND CRCallDateTime<'1/1/2003' AND CRCallResultCode ='CC'
ORDER BY CRRecordID

I then scroll to the 8000th row and copy the CrrecordID and run the following query

UPDATE dbo.berkleygroupinventory
SET listdescription ='berknew'
WHERE ListDescription ='BerkHold' AND CRRecordID <=5968432 AND CRCallDateTime ='1/1/2003' AND CRCallResultCode='CC'

I'm sure there's an easier way to do this, but I'm very new to SQL and haven't figured it out yet

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-06 : 16:46:17

Something like this should do it:

DECLARE @id int

SELECT @id = TOP 1 CRRecordID
FROM (
SELECT TOP 8000 ListDescription, CRRecordID
FROM dbo_BerkleyGroupInventory
WHERE ListDescription ="BerkHold" AND CRCallDateTime<'1/1/2003' AND CRCallResultCode ='CC'
ORDER BY CRRecordID ) t
ORDER BY CRRecordID DESC

UPDATE dbo.berkleygroupinventory
SET listdescription = 'berknew'
WHERE ListDescription = 'BerkHold' AND CRRecordID <= @id AND CRCallDateTime ='1/1/2003' AND CRCallResultCode='CC'

Tara Kizer
http://weblogs.sqlteam.com/tarad/

quincy
Starting Member

6 Posts

Posted - 2007-08-06 : 17:08:05

Thanks for the quick response!

I ran it but it came back with this:

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'TOP'.
Server: Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'ORDER'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-06 : 17:53:06

Sorry, try this:

DECLARE @id int

SELECT TOP 1 @id = CRRecordID
FROM (
SELECT TOP 8000 ListDescription, CRRecordID
FROM dbo_BerkleyGroupInventory
WHERE ListDescription ="BerkHold" AND CRCallDateTime<'1/1/2003' AND CRCallResultCode ='CC'
ORDER BY CRRecordID ) t
ORDER BY CRRecordID DESC

UPDATE dbo.berkleygroupinventory
SET listdescription = 'berknew'
WHERE ListDescription = 'BerkHold' AND CRRecordID <= @id AND CRCallDateTime ='1/1/2003' AND CRCallResultCode='CC'

You should test this in a test environment first in case my update statement doesn't work as desired.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

quincy
Starting Member

6 Posts

Posted - 2007-08-07 : 11:36:55

Ok... Well, I'm not sure how to create a test enviorment. I am brand new to this type of stuff but I learn pretty quick. I feel stupid right now though. lol.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-07 : 12:15:43

It's just my disclaimer. If my update doesn't work as desired, I just don't want the blame pushed to me for that. Always test things out first when given solutions from the Internet.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

quincy
Starting Member

6 Posts

Posted - 2007-08-08 : 16:32:23

Gotcha..... Thanks again.

quincy
Starting Member

6 Posts

Posted - 2007-08-18 : 14:10:26

The query ran but affected 0 rows.... :(

DECLARE @id int

SELECT TOP 1 @id =CRRecordID
FROM (
SELECT TOP 5 CRRecordID, listdescription
FROM dbo.BerkleyGroupInventory
WHERE ListDescription ='Berkcontrol' AND CRCallDateTime <'1/1/2003' AND CRCallResultCode ='CC'
ORDER BY CRRecordID ) t
ORDER BY CRRecordID DESC

UPDATE dbo.berkleygroupinventory
SET listdescription = 'berktest'
WHERE ListDescription ='Berkcontrol' AND CRRecordID = @id AND CRCallDateTime ='1/1/2003' AND CRCallResultCode ='CC'

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-18 : 14:39:25

that's because in your select your getting a to prow prior to '1/1/2003'
and in your update you want to update a row on '1/1/2003'
which doesn't exists for that condition.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-08-19 : 12:24:31

Try this:

UPDATE dbo.berkleygroupinventory
SET listdescription ='berknew'
WHERE CRRecordID in
(
SELECT TOP 8000 CRRecordID from dbo.berkleygroupinventory
WHERE ListDescription='Berkhold'
AND CRCallDateTime<'1/1/2003' AND CRCallResultCode ='CC'
ORDER BY CRRecordID
)

quincy
Starting Member

6 Posts

Posted - 2007-08-20 : 12:19:10

Thanks spirit1. That was a big "Duh" on my part.....
Zoroaster, That worked! Looks like a simple query too. Thank so much.

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-08-20 : 12:34:58

quote:
Originally posted by quincy

Thanks spirit1. That was a big "Duh" on my part.....
Zoroaster, That worked! Looks like a simple query too. Thank so much.

No problem, glad it worked!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources