so I have three tables orders,shipping,customers. And I want to update in customers table the import value to '88'. But in the PHP code I only have shipping.tracking_id. So I need to inner join the another tables (in the orders table I have the customers.id and the shipping.orderid) The tables looks like this:

Customers

Orders:

Shipping

I tried this code:

UPDATE customers inner join orders on orders.orderid = customers.id inner join shipping on shipping.orderid = orders.orderid SET customers.import = '88' WHERE shipping.tracking_id = '". $row["tracking_id"] ."';

Or this:

UPDATE customer set import WHERE (SELECT orders.customerid FROM shipping inner join orders on orders.orderid = shipping.orderid WHERE tracking_id = '12345678')

But no luck. Thank you!

CodePudding user response：

Try this solution

UPDATE `Customers` `cus` 
   SET `cus`.`import` = 88
WHERE EXISTS(
    SELECT * FROM `Shipping` `s`
        INNER JOIN `Orders` `o` on `o`.orderid = `s`.`orderid`
    WHERE `s`.`tracking_id` = 't5678' AND `cus`.`id` = `o`.`customerid`
);

Tested on Mysql

CodePudding user response：

If my understanding is correct, you should do the following:

• Join shipping with orders;
• Join orders with customers
• Add a condition on shipping.tracking_id

The query will look like this:

UPDATE customers
    SET import = '88'
FROM customers c
    JOIN orders o on c.id = o.customerid
    JOIN shipping s on o.orderid = s.orderid
WHERE s.tracking_id = 't1234';

CodePudding user response：

Try the below;

update customers set import = '88' where id = (select orders.customerid from orders where orders.orderid =(select shipping.orderid from shipping where shipping.tracking_id ='tracking_id'))