Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104

    Unanswered: UPDATE using a JOIN

    Hi all

    Whats the syntax for updating a table when using an inner join? I know how to use an INNER JOIN in a SELECT statement, and an UPDATE statement, but cant figure out how to put them together!

    Eg, i want to update the transactions table with the customer id from the customers table by joining the transaction_id from both tables?

    customers table
    ---------
    cutomer_id - transaction_id
    1 - 234
    2 - 345
    3 - 456


    transactions table
    ---------
    transaction_id - customer_id
    234 - ?
    345 - ?
    456 - ?


    thanks!
    Last edited by mattock; 08-29-06 at 13:20.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    proper syntax may depend on which database system you're using (which you neglected to mention)

    try this --
    Code:
    update transactions
       set customer_id = 
           ( select customer_id 
               from customers 
              where transaction_id = transactions.transaction_id )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    i will be using it in both MSSQL and MYSQL, i was just after the basic syntax which you've given me of using an update with a join, didn't think about doing it the way you suggested (the simple way!)

    All sorted now, thanks again rudy!

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Mattock

    This might be redundant as I know the overall goal of what you are attempting here but.... these two tables are not both part of your finalised, normalised database are they?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    hi flump

    No, the two tables i gave were just an example i made up. They are similiar however to the tables i'm trying to change, but i only had one of the duplicate columns in each table to match one to the other, i will be deleting one of the columns once i have matched them together to make a one-to-many relationship - i realise you may think it was an odd example to to give as it had the same columns in each table.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Super
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    not bad for a newbie eh! ...geetting there.....slowly....

Posting Permissions

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