I have 2 tables PO and PO_Details. I need to update table SKU_table. This is what I tried, but im doing something wrong.
UPDATE `SKU_table`.`Cost`
(SELECT `SKU_table`.`IDitem`, MAX( `PO`.`Cambio_BRL` * `PO_details`.`Valor Unitario` ) `UnitValue` FROM { oj `PurchaseDB`.`SKU_table` `SKU_table` LEFT OUTER JOIN `PurchaseDB`.`PO_details` `PO_details` ON `SKU_table`.`IDitem` = `PO_details`.`Produto` }, `PurchaseDB`.`PO` `PO`
WHERE `PO`.`ID` = `PO_details`.`ID_PO` GROUP BY `SKU_table`.`IDitem`)
SET `SKU_table`.`Cost` = `UnitValue`
WHERE `SKU_table`.`IDitem` =`SKU_table`.`IDitem`
tried also:
UPDATE `SKU_table`,`PO_details`, `PO`
LEFT JOIN `PO_details` ON `SKU_table`.`IDitem` = `PO_details`.`Produto`
LEFT JOIN `PO`
ON `PO_details`.`ID_PO` = `PO`.`ID`
SET
`SKU_table`.`Cost` = MAX( `PO`.`Cambio_BRL` * `PO_details`.`Valor Unitario` )
WHERE `SKU_table`.`IDitem` = `PO_details`.`Produto`
This does work but not Update:
SELECT `PO_details`.`Produto`, MAX( `PO`.`Cambio_BRL` * `PO_details`.`Valor Unitario` ) `UnitValue`
FROM { oj `PurchaseDB`.`PO` `PO` LEFT OUTER JOIN `PurchaseDB`.`PO_details` `PO_details` ON `PO`.`ID` = `PO_details`.`ID_PO` RIGHT OUTER JOIN `PurchaseDB`.`SKU_table` `SKU_table` ON `SKU_table`.`IDitem` = `PO_details`.`Produto` } GROUP BY `PO_details`.`Produto`
Tables:
mysql> DESCRIBE PO;
+----------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| ID | int | NO | PRI | NULL | |
| Data | datetime(6) | NO | MUL | NULL | |
| Envia Para | varchar(22) | NO | MUL | NULL | |
| Valor $ | decimal(19,4) | YES | | NULL | |
| Total R$ | decimal(19,4) | YES | | NULL | |
| Comprando de | varchar(20) | YES | | NULL | |
| Cambio_BRL | double | YES | | NULL | |
| NameCurancy | varchar(20) | YES | | NULL | |
| Boleto | varchar(100) | YES | | NULL | |
| Notes | varchar(0) | YES | | NULL | |
| Freight | decimal(19,4) | YES | | 0.0000 | |
| OPcostR$ | decimal(19,4) | YES | | 0.0000 | |
| TotalQTY | int | YES | | 0 | |
| ShipDate | datetime(6) | YES | | NULL | |
| Banking fee | decimal(19,4) | YES | | 0.0000 | |
| Alibaba fee | decimal(19,4) | YES | | 0.0000 | |
| Bacount | varchar(50) | YES | | NULL | |
| Bname | varchar(100) | YES | | NULL | |
| Baddress | varchar(100) | YES | | NULL | |
| Bswift | varchar(50) | YES | | NULL | |
| Bremarks | varchar(50) | YES | | NULL | |
| VenderInvoice | varchar(50) | YES | | NULL | |
| CBM | decimal(19,4) | YES | | 0.0000 | |
| POtradeID | int | YES | MUL | 0 | |
| SSMA_TimeStamp | binary(8) | NO | | NULL | |
+----------------+---------------+------+-----+---------+-------+
mysql> DESCRIBE PO_details;
+--------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+-------+
| ID | int | NO | PRI | NULL | |
| ID_PO | int | NO | MUL | 0 | |
| POtradeID | int | YES | MUL | 0 | |
| Produto | int | YES | MUL | 0 | |
| QTY | int | NO | | 0 | |
| Valor total | decimal(19,4) | NO | | 0.0000 | |
| ValorTotalPre | decimal(19,4) | YES | | 0.0000 | |
| Fornecedor | varchar(50) | YES | MUL | NULL | |
| Valor Unitario | decimal(19,4) | YES | | NULL | |
| ValorUnitarioPre | decimal(19,4) | YES | | NULL | |
| Tracking | varchar(255) | YES | | 0 | |
| Enviado | tinyint(1) | YES | | 0 | |
| Chego | tinyint(1) | YES | MUL | 0 | |
| Devolveu | tinyint(1) | YES | MUL | 0 | |
| URL | varchar(0) | YES | | NULL | |
| OrderNumber | varchar(0) | YES | | NULL | |
| DevClosed | tinyint(1) | YES | | 0 | |
| Notes | varchar(255) | YES | | NULL | |
| ReqDev | datetime(6) | YES | | NULL | |
| PaidTax | tinyint(1) | YES | | 0 | |
| Defects | tinyint unsigned | YES | | 0 | |
| Missing | tinyint unsigned | YES | | 0 | |
| BadBox | tinyint(1) | YES | | 0 | |
| GoodBox | tinyint(1) | YES | | 0 | |
| WrongItem | tinyint(1) | YES | | 0 | |
| MLfeePercent | double | YES | | NULL | |
| FreightTT | decimal(19,4) | YES | | 0.0000 | |
| OPcost | decimal(19,4) | YES | | 0.0000 | |
| TradeComtion | decimal(19,4) | YES | | 0.0000 | |
| BoxNumer | double | YES | | 0 | |
| ValorTotalPreExtra | decimal(19,4) | YES | | 0.0000 | |
| SSMA_TimeStamp | binary(8) | NO | | NULL | |
| Alibaba | int | YES | | NULL | |
+--------------------+------------------+------+-----+---------+-------+
mysql> DESCRIBE SKU_table;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| ID | int | NO | PRI | NULL | |
| SKU | varchar(40) | NO | UNI | NULL | |
| IDitem | int | NO | MUL | 0 | |
| Multiplyer | decimal(19,4) | YES | | 1.0000 | |
| SalesPrice | decimal(19,4) | YES | | 0.0000 | |
| Competitor | decimal(19,4) | YES | | 0.0000 | |
| LastCheck | datetime(6) | YES | | NULL | |
| Cost | decimal(20,2) | YES | | NULL | |
+------------+---------------+------+-----+---------+-------+
8 rows in set (0,00 sec)
here is what Is my results
mysql> SELECT `PO_details`.`Produto`,
MAX( `PO`.`Cambio_BRL` * `PO_details`.`Valor Unitario` ) `UnitValue`
FROM `SKU_table`
LEFT JOIN `PO_details` ON `SKU_table`.`IDitem` = `PO_details`.`Produto`
LEFT JOIN `PO` ON `PO_details`.`ID_PO` = `PO`.`ID`
GROUP BY `PO_details`.`Produto`;
+---------+--------------------+
| Produto | UnitValue |
+---------+--------------------+
| 3 | 4.289839970439189 |
| 8 | 41.886058806598705 |
| 11 | 7.750569952503958 |
| 18 | 8.602351999721657 |
| 22 | 4.466866468820436 |
| 23 | 70.58600803287013 |
| 28 | 15.799931545608105 |