Update statement 3

[Swi]

Is there a way where I can only update the top x records in an update statement? The reason I ask is because the below statement will not work. Also, is there a way to make the selection random? Thanks.

Option Explicit
Dim conn, rs, num_marked

Set conn = CreateObject("ADODB.Connection")

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\BCC\MM2010\Lists\;Extended Properties=dBASE 5.0;User ID=;Password="

conn.Execute "UPDATE [COLOR=red]TOP 2[/color] [TEST-A.DBF] SET [CODE1] = 'X' WHERE VAL([ZIP4]) BETWEEN 201200000 AND 201209999", num_marked

conn.Close
Set conn = Nothing

WScript.Echo num_marked1 + num_marked2 & " records marked for selection!"

Swi

 

[r937]

no, there isn't

why not run a SELECT to find the primary keys of the rows you want to update, and then just update those rows

with the SELECT, you can use TOP, and you can also grab randome rows that way

r937.com | rudy.ca

 

[PWise]

See:

http://www.tek-tips.com/viewthread.cfm?qid=1487014

conn.Execute "UPDATE [Select TOP 2  * from [TEST-A.DBF]  WHERE VAL([ZIP4]) BETWEEN 201200000 AND 201209999]. as a  SET [CODE1] = 'X' ", num_marked

 

[Golom]

Something like

UPDATE [TEST-A.DBF] SET [CODE1] = 'X'

WHERE KeyField IN (SELECT TOP 2 KeyField 
                   FROM [TEST-A.DBF]
                   WHERE VAL([ZIP4]) BETWEEN 
                         201200000 AND 201209999
                   ORDER BY Rnd(KeyField) )

Where "KeyField" is a Unique field in the table.

 

[Swi]

Golom,

Your example is the one that worked for me.

conn.Execute "UPDATE [TEST-A.DBF] SET [CODE1] = 'X' " & _
"WHERE [INPTRCRDNM] IN (SELECT TOP 2 [INPTRCRDNM] " & _ 
"FROM [TEST-A.DBF] WHERE VAL([ZIP4]) BETWEEN 201200000 " & _
"AND 201209999 ORDER BY Rnd([INPTRCRDNM]))", num_marked2

Although, why wouldn't this work (this is what I tried because I could not connect to TekTips for a few hours)?

conn.Execute "UPDATE [TEST-A.DBF] SET [CODE1] = 'X' FROM " & _
"(SELECT TOP 2 * FROM [TEST-A.DBF] ORDER BY RND(VAL([ZIP4]))) WHERE " & _
"VAL([ZIP4]) BETWEEN 201120000 AND 201129999", num_marked2

I get a Syntax error (missing operator) in query expression "X' FROM (SELECT TOP 2 * FROM [TEST-A.DBF] ORDER BY RND(VAL([ZIP4])))

Swi

 

[Golom]

It doesn't work because an UPDATE statement doesn't have a FROM clause.

The Table to be updated is specified between the UPDATE and SET keywords.

 

[r937]

an UPDATE statement doesn't have a FROM clause

well, actually, it can have, although in MS Access syntax the FROM clause is implied



sample valid Access sql --

update table1
inner 
  join table2 
    on table1.keyfld = table2.keyfld
   set table1.fldx = table2.fldy
 where table2.fldz = 'foo'

in SQL Server, the same syntax would be --

update table1
   set fldx = t2.fldy
  from table1 t1
inner
  join table2 t2
    on t1.keyfld = t2.keyfld
 where t2.fldz = 'foo'

and yes, i do have to look this up every time

;-)

r937.com | rudy.ca

 

[Swi]

So there would really be no problem with doing this instead?

conn.Execute "UPDATE [TEST-A.DBF] SET [CODE1] = 'X' " & _
"WHERE [ZIP4] IN (SELECT TOP 2 [ZIP4] " & _ 
"FROM [TEST-A.DBF] WHERE VAL([ZIP4]) BETWEEN 201200000 " & _
"AND 201209999 ORDER BY Rnd([ZIP4]))", num_marked2

I can not be guaranteed that I will have a unique field on the file as it is a DBF file and these are coming from multiple sources. Thanks again.

Swi

 

[Swi]

r937,

Thank you for your insight as well.

Swi

 

[PWise]

does this work

conn.Execute "UPDATE (Select TOP 2  * from [TEST-A.DBF]WHERE VAL([ZIP4]) BETWEEN 201200000 AND 201209999 ORDER BY RND(VAL(ZIP4))) a  SET [CODE1] = 'X'", num_marked

 

[Swi]

Yes, thank you.

Swi