Update only a set percentage?

[kp1279]

I am fairly new to SQL, but here it goes.

What I am trying to do is run two different procedures within one.

One sets a value within existing rows to show that someone is working on the records, and the other copies those records to another table, where editing can be done.

The reason it has to be this way, is that when the user has finished with the updating, another procedure is run that records and captures the changes made, and by who.

This works purfect.

Anyway, here is my problem: -

What I want to do is to use the 'TOP ** PERCENT' type arguement, like you can with a simple select statement, but this time I want it to say something like -

UPDATE the Top ** Percent Admin_Table SET edited_by=suser_name() where admintag='1' and edited_by = null.

The code is (With bits changed to protect the origin): -



begin tran

update admin_TABLE set currently_edited_by=suser_sname()

where currently_edited_by is null and admintag=1

insert edited_logs ( ## FIELD CODES ## )

select ## FIELD CODES ##

from admin_TABLE

where currently_edited_by=suser_sname() and admintag=1

commit tran


But in this arguement it doesn't like the Top ** Percent, is there another way, either to re-word this part, or to change it so that it updates the first so many records instead of a percentage.

I am using server 2000, and Access 2000.

Bit vaugue I know but any help is appreciated.

 

[SQLSister]

Join to a select that picks the top X% of the records as a derived table. Something like:

update table1
set field1 = t1.field1
from table1 t join 
(select top 10 percent field1 from table1) t1 on t.field1 = t1.field1
where t. field2 = 'test'

Questions about posting. See faq183-874

 

[Catadmin]

You can't use keywords like TOP or DISTINCT (etc.) in an UPDATE statement. They can only be used in SELECT statements.

So, SQLSister is correct. You're going to have to use a join in your Update's FROM clause to get the data you want.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"