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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists