update multiple records

[ShMiL]

I tried something like:

.execute ("UPDATE tbl1 SET fld1="& val1 &" WHERE id=1;UPDATE tbl1 SET fld1="& val1 &" WHERE id=2;UPDATE tbl1 SET fld1="& val1 &" WHERE id=N;")

And it didn't work.
Any other idea (without writing '.execute' N times).

Thanks

[raf]

.execute ("UPDATE tbl1 SET fld1="& val1 &" WHERE id In (1,2,...)")

the ... of course mean 'and othr id's' --> they should not be in the actual statemnts. A list of other id's should be there instead.

You can dynamically build the In-collection (for isntance when loopin through the formfield)-collection and then do a.

UPDATE tbl1 SET fld1="& val1 &" WHERE id In (" & coll & ")

[ShMiL]

Thanks
I'm getting closer...
But my problem is that fld1's value (val1) is changing between each ID...
If I had to do it manually, it'd be:

.execute ("UPDATE tbl1 SET fld1="& val1 &" WHERE id=1;")
.execute ("UPDATE tbl1 SET fld1="& val2 &" WHERE id=2;")
.execute ("UPDATE tbl1 SET fld1="& valN &" WHERE id=N;")

It seems like only the ID's are changing in your query, and I need to change both ID's and val's.

Thanks for further help

[raf]

Originally posted by ShMiL
It seems like only the ID's are changing in your query, and I need to change both ID's and val's.

Seems like in your query, it were also only the id's that were changing:

Originally posted by ShMiL
.execute ("UPDATE tbl1 SET fld1="& val1 &" WHERE id=1;UPDATE tbl1 SET fld1="& val1 &" WHERE id=2;UPDATE tbl1 SET fld1="& val1 &" WHERE id=N;")

If the values differ, then this is a complete other operation, and then you'll need to either do it one record at a time, or do a recordset update.

You can still do it dynamically. Run a search here (quickest way to a problem) and you'll find plenty of threads where i and others have posted examplecode.
Like

http://www.codingforum.net/showthread.php?s=&threadid=27902&highlight=update+loop

http://www.codingforum.net/showthread.php?s=&threadid=27069&highlight=update+loop

http://www.codingforum.net/showthread.php?s=&threadid=22753&highlight=update+loop

[ShMiL]

I looked at the links...
And what I understand from it, is that I can't do it without doing multiple .execute's
(http://www.codingforum.net/showthrea...ht=update+loop --> raf)

Am I right?

[raf]

No. You can not. All possible options are explained inside that link.

You can not update one field if you have differnt values for the records you need to update. You can however reduce the number of updates, by grouping the records, based on the new value for the variable). You then have 1 update for all records that need to get the same value for the variable. Like

update table set var1 = " & value1 & " where id In(" & collection1 & ")
update table set var1 = " & value2 & " where id In(" & collection2 & ")
update table set var1 = " & value3 & " where id In(" & collection3 & ")

The collection are then build like explained in the links i posted.
But this is only useful if the number of records with the same new value is high enough.

Else just use the 1 record at a time update.

[ShMiL]

Not only they are not high enough, they are unique!
ok,
I'll do multiple queries.

Thank you verymuch!

[raf]

You're welcome.

[zzg]

Why don't you use a loop?

[raf]

Originally posted by zzg
Why don't you use a loop?

Good idea
Maybe check the links and you'll see that the code in each link uses a 'for each'-loop to iterate through the formscollection.