Question 22096 in Mysql Question posted in
The official MySQL documentation can be found here.

SQL syntax error – multiple values from two tables & insert total – Mysql

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.

Tags:

2

Answers


  1. 0

    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";
    Login or Signup to reply.
  2. 0

    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)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search