Raizan Raizan -4 years ago 77
MySQL Question

Error 1054 on Update mySQL even if table exists

here i define my code in MySQL its to update a "stock" kind of system but i keep getting the error that my table "ecomendas" does not exist even if its created multiple times. Don't know how to solve it and all i want it to do is to remove from other table that has the amount of items in stock ("encomendas" = "orders").

INSERT INTO encomendas (
Encomenda_ID,
Cliente_ID, #ID Cliente
Item_ID, #ID Stock
Unidades, # Quantidade Ex: 100 (sacos)
Valor_Encomenda,
Estado_PagamentoID
)
values(
null, #ID ENCOMENDA
1, #ID CLIENTE
1, #ID STOCK
1, # QUANTIDADE
10, #Valor Encomenda
1 # Estado de Pagamento
);

UPDATE stock as s
SET s.Quantidade_Item = s.Quantidade_Item - encomendas.Unidades
WHERE encomendas.Item_ID = s.Item_ID;

Answer Source

I think you want to use a JOIN as below:

UPDATE stock as s INNER JOIN encomendas as e ON e.Item_ID = s.Item_ID
SET s.Quantidade_Item = s.Quantidade_Item - e.Unidades 

does that do what you expect?

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download