UPDATE from a query with conditions

Question

UPDATE from a query with conditions

Navigation

#1 by (0 votes)

0

with this query:

SELECT id AS wrcId, st AS wrcSt, detail AS wrcDetail,
    CONVERT(SUBSTR(detail,(LOCATE('#',detail) +1)), UNSIGNED INTEGER)AS ordenId
FROM box_12.warehouse_rc
WHERE LOCATE('#',detail)>0 AND id IN(
SELECT id FROM box_12.esale_rc WHERE kind=30)
ORDER BY OrdenId DESC;

I get these results (89 lines in total),

| id   |  st |              detail               | OrdenId  |
|:----:|:---:|:---------------------------------:|:--------:|
| 4273 | 16  | eSale invoice # 5117 (Traje Baño) | 5117     |
| 4272 | 16  | Sale invoice # 5116 (Traje Baño)  | 5116     |
| 4270 | 16  | eSale invoice # 5115 (Outlet)     | 5115     |
| 4271 | 16  | eSale invoice # 5114 (Traje Baño) | 5114     |
| ...  | ... | ...                               | ...      |
| ...  | ... | ...                               | ...      |

I need

Do an UPDATE to the st field of the warehouse_rc table where the query above gives me the id based on the OrderId which are 2 different tables.

I hope to have explained.

mysql query mysql-workbench

asked by Hector Godoy 08.09.2018 в 02:33

source

1 answer

Know the name of the first table of an excel sheet in VB.NET by OleDB How can I get the user to download an Excel file that is in a virtual path?

score 0 · Answer 1

@PauloUrbanoRivera, of course, but I got the answer

Error Code: 1093. You can't specify target table 'warehouse_rc' for update in FROM clause

UPDATE box_12.warehouse_rc SET st = 0
WHERE(SELECT 
    CONVERT(SUBSTR(detail,(LOCATE('#',detail) +1)), UNSIGNED INTEGER)AS ordenId
FROM box_12.warehouse_rc
WHERE id IN(
    SELECT id FROM box_12.esale_rc WHERE kind=30)
);