Convert from Oracle to MySQL, tricky query

[roamdave]

Hi,

I have a query i use in my JDBC connected application to Oracle. I am trying to connect it to MySQL and rewrite those queries that MySQL cannot cope with. The following one has me beat. Any suggestions?

UPDATE EQUITIES_WARRANTS_PRICE
SET RIC=(SELECT NEW_RIC FROM MAINTENANCE_OPERATIONS
WHERE OPERATION='MR' AND
EQUITIES_WARRANTS_PRICE.RIC=RIC)
WHERE RIC IN
(SELECT RIC FROM MAINTENANCE_OPERATIONS
WHERE OPERATION='MR')

 

[swampBoogie]

UPDATE EQUITIES_WARRANTS_PRICE, MAINTENANCE_OPERATIONS
SET EQUITIES_WARRANTS_PRICE.RIC= MAINTENANCE_OPERATIONS.NEW_RIC 
WHERE MAINTENANCE_OPERATIONS.OPERATION='MR' AND
EQUITIES_WARRANTS_PRICE.RIC=MAINTENANCE_OPERATIONS.RIC

 

[roamdave]

Hi,

I Tried

UPDATE EUROPEAN_UNIT_TRUST_PRICE
SET EUROPEAN_UNIT_TRUST_PRICE.RIC = MAINTENANCE_OPERATIONS.NEW_RIC
WHERE EUROPEAN_UNIT_TRUST_PRICE.RIC = MAINTENANCE_OPERATIONS.OLD_RIC
AND EUROPEAN_UNIT_TRUST_PRICE.TRADING_DATE <
MAINTENANCE_OPERATIONS.RECORD_DATE
AND MAINTENANCE_OPERATIONS.OPERATION='MC'

General error: Unknown table 'MAINTENANCE_OPERATIONS' in where clause

And

UPDATE EUROPEAN_UNIT_TRUST_PRICE, MAINTENANCE_OPERATIONS
SET EUROPEAN_UNIT_TRUST_PRICE.RIC = MAINTENANCE_OPERATIONS.NEW_RIC
WHERE EUROPEAN_UNIT_TRUST_PRICE.RIC = MAINTENANCE_OPERATIONS.OLD_RIC
AND EUROPEAN_UNIT_TRUST_PRICE.TRADING_DATE <
MAINTENANCE_OPERATIONS.RECORD_DATE
AND MAINTENANCE_OPERATIONS.OPERATION='MC'

Syntax error or access violation: You have an error in your SQL syntax near
' MAINTENANCE_OPERATIONS
SET EUROPEAN_UNIT_TRUST_PRICE.RIC = MAINTENANCE_OPERATIO' at line 1

I read the MySQL documentation and it looks from the syntax for UPDATE and DELETE that it is not possible to update or delete based on a join to another table. Is this so?

Rgds,

David

 

[swampBoogie]

It depends on which version you have. The second query should work in version 4.0.4 and later.

 

[roamdave]

Cool, looks like I'd better move up from 3.2?.47 on Mandrake 8.2 then.