Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    Posts
    3

    Unanswered: Update Table A based on Table B

    I am new to MYSQL and this forum, so please bear with me. I have been trying to figure this one out, but have so far drawn a blank. If anyone could help me out with the right SQL syntax for an Update Query, I would be most grateful, thanks.

    I have two tables, A and B.
    Table A contains:
    match_key
    corporate_stays
    Table B contains:
    match_key
    corporate_stay_category

    For each record on Table A, there will be one or many table B records, using the match_key as the join on both tables. Each matching Table B record will have either corporate_stay_category = "C", or blank (Not Null)

    I want to be able to Update the corporate_stays on each record on table A, with a count of matching table B records that have a corporate_stay_category = "C".

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    what version of mysql are you using? Multi table updates are supported in 4.0.4 and newer IIRC.

  3. #3
    Join Date
    Nov 2004
    Posts
    3
    Recently upgraded to MYSQL v 4.1.17!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why are you trying to update a value based on data within the database. You should be able to get to the value using a query. Otherwise you run the risk that the information in the table will be out of date

    What you are looking for is a relatively simple query
    select count(<column to count>) NoStays from
    <table>
    <specify the join>
    <specify the where criteria>

    the join will probably be
    Join A.match_key on B.match_key
    group by a.match_key
    having corporate_stay_category="C"
    or somethign similar - sorry away form the Linux / MySQL box today so can't test it.
    If all else fails look in the manual for the join syntax and SQL aggregate functions, or dare I say it get a decent SQL or MySQL reference.

  5. #5
    Join Date
    Nov 2004
    Posts
    3
    I have already achieved the SELECT, and I appreciate that the data will get out of date. (Our database is updated once a month by importing data from the client, it's not a transaction database, but a marketing database that is used for statistical analysis)
    I will need to do several of these updates each month to classify each record on table A. These classifications are then used to select certain categories of records for direct mailing.
    Hope that I have explained this succinctly, in essence it is important that we carry out these updates once a month.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    clive, i've tested this a few ways from sunday, and the multi-table update syntax doesn't seem to like the GROUP BY clause

    you will have to do the aggregation of B rows into a temp table, and then do the update from that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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