Best guess:
Oracle allows "dirty" reads. This means that if a table/row is locked
(for
update or delete transactions in progress) then when another thread
attempts
to access the data Oracle will return the last known value. This is
good in
one respect because it prevents deadlocking. Not all databases allow
this.
Access is a single-threaded database by nature so concurrent requests
are
queued up and must wait for other processes to finish.
Your query locks the category table with the UPDATE statement then
tries to
read from it with the SELECT statement. The SELECT statement must wait
until the UPDATE statement is done to continue, but the UPDATE
statement
can't finish without the SELECT statement. Instant deadlock.
I'm basing this on some known facts and a few assumptions so don't take
it
as gospel. You may also want to try aliasing your tables so that the
query
can tell the diff between the two category declarations...
UPDATE category c1
SET ... = (SELECT ... FROM section s, category c2 WHERE...)
+-----------------------------------------------+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
Telecomunication Systems
[EMAIL PROTECTED]
+-----------------------------------------------+
"...'If there must be trouble, let it be in my day, that my child may
have
peace'..."
- Thomas Paine, The American Crisis
-----Original Message-----
From: Dave Watts [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 03, 2002 9:43 AM
To: CF-Talk
Subject: RE: SQL GURUS?
> I am having trouble with a query:
>
> Works fine in Oracle...
>
> <cfquery name="addtypes" datasource="kriskart">
> Update category
> set tutorial = (Select max(section.tutorial)
> from section, category
> where category.code = section.catalogid)
> </cfquery>
>
>
> Anyidea why Access tells me this is an "[Microsoft][ODBC
> Microsoft Access Driver] Operation must use an updateable
> query."
I don't use Access enough to know whether you can use a subquery in an
UPDATE statement, but I've seen that error message before when the
Access
database file was marked as read-only on the filesystem.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
______________________________________________________________________
Why Share?
Dedicated Win 2000 Server � PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation � $99/Month � Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
