Jeremy John Jeremy John - 5 months ago 10
MySQL Question

UPDATE table with WHERE multiple condition from another table

I have 2 tables:

users

| id | amount |
---------------
| 1 | 10 |
| 2 | 20.50 |
| 3 | 0 |
| 4 | 80 |
| 5 | 0 |
---------------


vehicle_travel

| id | user_id | costprice | status |
--------------------------------------
| 1 | 1 | 80.00 | active |
| 2 | 1 | 20.00 | expired |
| 3 | 2 | 130.50 | active |
| 4 | 5 | 325.00 | active |
| 5 | 3 | 99.50 | expired |
--------------------------------------


I want to
UPDATE
users table and
SET
users.amount to users.amount+vehicle_travel.costprice
WHERE
the users.id equals to vehicle_travel.user_id
AND
vehicle_travel.status equals to 'expired'

This is my full query:

UPDATE users
SET users.amount=users.amount+vehicle_travel.costprice
WHERE users.id=vehicle_travel.user_id
AND vehicle_travel.status='expired'


But i keep getting unknown column errors from phpMyAdmin.

What am i doing wrong?

Answer

Try this;)

UPDATE users 
INNER JOIN vehicle_travel
ON users.id = vehicle_travel.user_id
AND vehicle_travel.status = 'expired'
SET users.amount = users.amount + vehicle_travel.costprice 

EDITED:

UPDATE users 
INNER JOIN (
    SELECT SUM(costprice) AS costprice, user_id
    FROM vehicle_travel
    WHERE status = 'expired'
    GROUP BY user_id
) vehicle_travel
ON users.id = vehicle_travel.user_id
SET users.amount = users.amount + vehicle_travel.costprice 
Comments