SQL syntax error - multiple values from two tables & insert total-CodePudding

I need some help with the query below - I am trying to pull information regarding price and multiply with the quantity & insert the sum into the table. So far I have,

update passenger_baggage 
SET passenger_baggage.total_baggage_cost=passenger_baggage.passenger_baggage_quantity*baggage_type.baggage_type_cost
FROM passenger_baggage INNER JOIN baggage_type
ON passenger_baggage.passenger_baggage_id = baggage_type.baggage_type_id
WHERE  passenger_id = "3";

and getting this error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM passenger_baggage INNER JOIN baggage_type ON passenger_baggage.passenge...' at line 3

Expecting the query to multiply the two values & insert the total.

CodePudding user response：

There is no FROM clause in an UPDATE query.

Try with this modified query:

update passenger_baggage
INNER JOIN baggage_type
ON passenger_baggage.passenger_baggage_id = baggage_type.baggage_type_id 
SET passenger_baggage.total_baggage_cost = passenger_baggage.passenger_baggage_quantity * baggage_type.baggage_type_cost
WHERE  passenger_id = "3";

CodePudding user response：

Try this:

UPDATE passenger_baggage, baggage_type 
SET passenger_baggage.total_baggage_cost = passenger_baggage.passenger_baggage_quantity * baggage_type.baggage_type_cost 
WHERE passenger_baggage.passenger_baggage_id = baggage_type.baggage_type_id AND passenger_id = "3";

Saw an example from the MySQL doc (https://dev.mysql.com/doc/refman/8.0/en/update.html)