问题描述:

This is my select query which works fine :

 SELECT PRICE.PRICEDATETO AS PDF

,ATTR.PRICEDATEFROM AS ADF

,ATTR.PRICEDATETO AS ADT

FROM RT_DATA_CORE DCORE

INNER JOIN RT_DATA_PRICE PRICE ON DCORE.ID=PRICE.ID

INNER JOIN RT_PRODUCT_CORE PCORE ON PCORE.ID=PRICE.PRODUCTID

INNER JOIN RT_DATA_PRICEATTRIBUTES ATTR ON

CONCAT(ATTR.PRODUCTID,'_V')=PCORE.SKU

AND PRICE.ID='PRICE-26095657'

AND ATTR.PRICEDATETO>NOW()

ORDER BY ATTR.PRICEDATETO ASC LIMIT 1;

I need to update it to ADF=PDF

I am trying this which gives me an error:

mysql> UPDATE RT_DATA_PRICE PRICE INNER JOIN (SELECT PA.PRICEDATETO AS DATER,PCORE.ID AS PID FROM RT_DATA_PRICEATTRIBUTES PA,RT_PRODUCT_CORE PCORE WHERE PA.PRICEDATETO > NOW() AND PCORE.SKU = CONCAT(PA.PRODUCTID,'_V') ORDER BY PA.PRICEDATETO ASC LIMIT 1)AOP ON AOP.PID=PRICE.PRODUCTID AND PRICE.ID='PRICE-26095657' SET AOP.DATER=PRICE.PRICEDATETO;

ERROR 1288 (HY000): The target table AOP of the UPDATE is not updatable

网友答案:

The table RT_DATA_CORE is not being used so I've removed it, try this

UPDATE RT_DATA_PRICE PRICE, RT_PRODUCT_CORE PCORE, RT_DATA_PRICEATTRIBUTES ATTR
SET ATTR.PRICEDATEFROM = PRICE.PRICEDATETO 
WHERE
    PCORE.ID=PRICE.PRODUCTID 
    CONCAT(ATTR.PRODUCTID,'_V')=PCORE.SKU 
    AND PRICE.ID='PRICE-26095657' 
    AND ATTR.PRICEDATETO>NOW();
相关阅读:
Top