update query help

Hi guys

i'm a little difficulty in updating one table, but using two where clauses from two different tables.

basically i have 4 tables, customers, packages, status and transactions.

a customer can have several packages, each package has a status, and each package has a transactions.

i already know the customer_id, the transaction table already has the customer_id, and package_id(s) allocated on insert.

i need to be able to update the transaction table where the customer_id is the one i already have and where the package status is 'Pending'.

i cant seem to work out how i get the package_status from the packages table and include it in the update query where clause.

any help would be great.

thanks in advance

It sounds like you are storing an extra foreign key in the transactions table that you really don't need. If a customer has a package that has a transaction, then you know the customer ID of the transaction based on the package it belongs to, correct? So you shouldn't store customer ID in transaction (unless there is a highly compelling reason to do so based on query performance).

At any rate, you can use a subquery in an UPDATE (version 4.1+).

UPDATE transactionTable as t
SET t.field1 = 'blah'
WHERE t.package_id = (
SELECT p.package_id
FROM packageTable as p
JOIN statusTable as s
ON p.package_id = s.package_id
WHERE p.customer_id = '999999'
AND s.statusCode = 'Pending')