UPDATE certain number of records? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Other Forums

MS Access

UPDATE certain number of records?

Author

Topic

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2002-08-02 : 10:17:02

I have 50 records lets say, all with the ID = 1 and Piece = 2 and Date = NULL. I want to applay a date, the same date to 25 of them. Is there a way this could be done using the update statment?


"UPDATE TOP 25 MyTable 
SET Date = #SomeDate# 
WHERE ID = 1
AND Piece = 2
AND Date IS NULL
ORDER BY ID DESC"

I tried this but I get the error message saying there is a syntax error in my Update statement. Any help? How can I do this??

Mike B

Edited by - MikeB on 08/02/2002 10:23:43

royv
Constraint Violating Yak Guru

455 Posts

Posted - 2002-08-02 : 10:34:34

Oops, I gave a SQL solution

Edited by - royv on 08/02/2002 10:35:19

royv
Constraint Violating Yak Guru

455 Posts

Posted - 2002-08-02 : 10:47:49

I know this is kind of messy, but you could select the 50 records you are interested into a new table with an AutoID column, and then you could base your update statement with an inner join to the old table.

*************************
Someone done told you wrong!

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2002-08-02 : 10:57:35

take out the order by part of the statement. i don't think you can have it in an UPDATE statement. If you could would you need it anyway ??

Paul

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2002-08-02 : 11:10:55

I removed the ORDER BY from the statement and it still says Syntax error. And no I didn't need the order by since all the criteria is the same :). I guess it is safe to assume you cannot use the TOP keyword with the Update statement either!

Mike B

Edited by - MikeB on 08/02/2002 11:13:59

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2002-08-02 : 11:32:27

yep no TOP either.

Does it work now ??

Paul

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2002-08-02 : 15:13:34

Yes it does update without the TOP keyword but this just takes me back to my original question of is there a way to update only 25 of the original 50 records. What I was trying to avoid was selecting 25 of the records and going through a "while" loop to update each individual record.

Thanks for your replies :).

Mike B

Jeepaholic
Starting Member

36 Posts

Posted - 2002-11-20 : 15:10:36

Probably a little late, but for anyone that needs to know - this is a good way to accomplish this:

BTW, "ID" needs to be a primary key.

UPDATE MyTable
SET Date = #SomeDate#
WHERE ID = 1
AND Piece = 2
AND Date IS NULL
AND (ID IN
(SELECT TOP 25 ID
FROM MyTable))
ORDER BY ID DESC

Edited by - Jeepaholic on 11/20/2002 15:11:14

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-20 : 15:58:25

I think the problem was ID was not the primary key ....

Mike B -

Is there a primary key in the original table or any distinguishing features in the records? i.e., each has a different description or something?

Also -- you could create a new field, call it temp, and make it an autonumber int. It will automatically populate. Then just choose the range from this field that you wish to update, and delete the field when you are done.

- Jeff

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-20 : 16:04:28

Any reason why you couldn't just do:

SET ROWCOUNT 25
UPDATE MyTable
SET Date = #SomeDate#
WHERE ID = 1 AND Piece = 2 AND Date IS NULL
SET ROWCOUNT 0

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-20 : 16:08:21

Can you set the rowcount in Access ??

- Jeff

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-20 : 16:18:35

!#@%!$#^%#%! didn't read the forum title...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources