Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Kal
    Kal is offline
    Regular Coder
    Join Date
    Dec 2005
    Posts
    309
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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+).
    Code:
    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')


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •