UPDATE using a JOIN

mattock · #1 (**permalink**) 08-29-06, 12:15

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!

r937 · #2 (**permalink**) 08-29-06, 12:27

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 )

mattock · #3 (**permalink**) 08-29-06, 13:19

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!

pootle flump · #4 (**permalink**) 08-30-06, 06:58

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?

mattock · #5 (**permalink**) 08-30-06, 07:12

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.

pootle flump · #6 (**permalink**) 08-30-06, 08:02

Super

mattock · #7 (**permalink**) 08-30-06, 08:18

not bad for a newbie eh!

...geetting there.....slowly....

Internet Services

Web Development

Digital Marketing

Consumer Tech