SQL update Query with values coming from other Tables-CodePudding

I'm very bad at SQL so I'm struggling to UPDATE using values coming from separate tables:

I need use values inside 2 different tables as well as values from PHP script as part of an update query into a third table. My current SQL

UPDATE 
    order1
SET 
    Reorder_Time=supplier.Reorder_Time
    Amount_Ordered="123456789"
    Stock_Amount=product.Stock_Amount
FROM 
    supplier,product
WHERE 
    Order_ID="123456789"

:variables is just values from PHP and table and product are just other tables in my database

CodePudding user response：

update order set 
Reorder_Time=supplier.Reorder_Time
Amount_Ordered="123456789"
Stock_Amount=product.Stock_Amount
from  
order 
join supplier 
on supplier.orderId = order.Id 
join production 
on supplier.[productFK or Id] = product.[Id or supplierFK]
where orderId = 123456789

CodePudding user response：

I'm unfamiliar with the php script portion of your question but in order to update a table with the value of another table you might find success using the following INSERT INTO statement:

INSERT INTO table_abc
SELECT * FROM table_xyz WHERE condition

This code assumes that table_abc and table_xyz have the same structure. SQL will first execute the SELECT statement and grab the data that meets the WHERE condition. It will then grab those lines and insert them into table _abc.

Please try to provide more information on the structure of the 3 tables. The above code will probably not work since it assumes the structure is the same. By structure, I mean same number and names of columns.

Check this out as well: https://www.w3schools.com/sql/sql_insert_into_select.asp