View Full Version : update query help

04-10-2007, 11:18 AM
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

04-10-2007, 06:45 PM
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')