#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    12
    Rep Power
    0

    Many to one update sql


    Hello:

    I am having a problem with the proper SQL to accomplish the following.

    I am using MySQL. I have 2 tables, both tables contain 2 columns, column a and column b. In table 1 column a is the key and therefore unique. In table 2 the value in column a can be repeated many times.

    What I want to do is update column b in table 1 from column b in table 2 from all rows in table 2 where t1.columna = t2.column a

    Thanks in advance
  2. #2
  3. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,126
    Rep Power
    9398
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    12
    Rep Power
    0

    Join in update


    I have tried as
    UPDATE table1 t1, table2 t2
    SET t1.columnb = t1.columnb + t2.columnb
    WHERE t1.columna = t2.columna

    This does not work
  6. #4
  7. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,663
    Rep Power
    1958
    Please give more information than "this does not work".

    Try with the explicit join syntax:
    UPDATE table1 t1 INNER JOIN table2 t2 ON t1.columna = t2.columna
    SET t1.columnb = t1.columnb + t2.columnb
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    12
    Rep Power
    0

    More info


    I have tried both ways, This is the detai. Table 1 contains customers (customer number,....,arbalance) Table 2 contains invoice information (customer number,...,invoice amount). There can be several invoices in table 2 for any given customer in table 1. In my test I have created a customer file with only 1 customer, say customer 1. The starting arbalance is 0. The test invoice table contains 2 invoices, both for customer 1. (invoice 1, customer 1, amount 200 and invoice 2, customer 1, amount 300). I want to update the custoner tables arbalance with all matching invoices, in the test case there are only 2 and they both match. No mater how I try the update only the 1st invoice updates the customer file. After the update I expect that arbalance will be 500 (starting 0 + 200 + 300). It always equals 200.
  10. #6
  11. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,663
    Rep Power
    1958
    Just curious why you want to store SUM value, since you always can call SUM() for this tasks?
    For example, the below query will give the total invoice amount for each customer (number):
    Code:
    SELECT customer_number, SUM(invoice_amount)
    FROM Invoice
    GROUP BY customer_number
    Furthermore, when and where did you expect to do the update call?
    t1.columnb = t1.columnb + t2.columnb will not take into consideration what had already been added to the sum.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    12
    Rep Power
    0

    Solution


    Hello:

    I did find a solution very similar to what you suggested.

    UPDATE t1
    SET columnb = columnb + (SELECT SUM(columnb) FROM
    t2 WHERE t1.columna = t2.columna)

    Thanks

IMN logo majestic logo threadwatch logo seochat tools logo