更新子查询不工作的mysql

时间:2021-07-23 00:10:27

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

我需要将其更新为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

mysql> UPDATE RT_DATA_PRICE PRICE INNER JOIN(选择PA.PRICEDATETO作为DATER,PCORE.ID作为PID来自RT_DATA_PRICEATTRIBUTES PA,RT_PRODUCT_CORE PCORE,其中PA.PRICEDATETO> NOW()和PCORE.SKU = CONCAT(PA.PRODUCTID,'_ V')令由PA.PRICEDATETO ASC限制1)AOP在AOP.PID = PRICE.PRODUCTID和PRICE.ID ='PRICE-26095657'Set AOP.DATER = PRICE.PRICEDATETO;错误1288(HY000):UPDATE的目标表AOP不可更新

1 个解决方案

#1


0  

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

表RT_DATA_CORE未被使用,所以我已将其删除,试试这个

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();

#1


0  

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

表RT_DATA_CORE未被使用,所以我已将其删除,试试这个

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();