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

    Join Date
    Jun 2012
    Posts
    150
    Rep Power
    3

    Question Query/insert between 3 tables with only 2 columns matching between any two tables?


    Hi all,

    I've got a situation where I need to populate a new table column with all of the data from another table column.

    This is the set up (the tables have more columns than this, but this is the issue):

    Table A
    Column 1
    Column 2 <-- New, empty.

    Table B
    Column 2
    Column 3

    Table C
    Column 1
    Column 3

    I added a new column called 2 (just like exists in table B), and now I need to add the data from Table B's column 2 to into Table A's new column 2.

    So it seems to me that I need to somehow select all Column 2 from table B, where Column 3 matches between tables B and C and Column 1 matches between tables A and C.

    How do I write the query to do that?

    Essentially, how do I write a query to do this:

    select Column 2, Column 3 from TABLE B
    while results
    Select Column 1, Column3 From Table C WHERE Column 3 = result of Column 3 above
    while results
    Insert into Table A Column 2 result WHERE Column 1 = Column 1 from result from C above

    [Edit: add pseudo query]
    Last edited by we5inelgr; March 21st, 2013 at 03:11 PM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    you want a joined update --
    Code:
    UPDATE TableA
    INNER
      JOIN TableC
        ON TableC.Column1 = TableA.Column1
    INNER
      JOIN TableB
        ON TableB.Column3 = TableC.Column3
       SET Table1.Column2 = TableB.Column2

    Comments on this post

    • we5inelgr agrees : excellent suggestion, worked perfectly.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    150
    Rep Power
    3
    awesome. many thanks!
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    thanks for fixing the typo in the SET clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo