Update... table1.column=table2.column where table1.id=table2.number

[geo__]

Hi all,
I want to update the columns of one table to the columns of another table based on a common field.
This is what I tried but it's not working:

UPDATE TABLE1 SET TABLE1.NAME = TABLE2.NAME
WHERE TABLE1.ID=TABLE2.CARD_NUMBER

(ID and card number are unique, card numbers is a subset of id,
e.g. ID can be 1..10 and cardnumber 1,5,7)

Any ideas?

Thnx

 

[CJBIRKIN]

Hi

Can you be a bit more specific about the problem i.e what is happening when you run your query?

Chris

 

[geo__]

Figured it out

Well I have to join the two tables based on the common field, then do the set. Here it is:

UPDATE TABLE1 INNER JOIN TABLE2
ON [TABLE1].[ID]=[TABLE2].[CARD_NUMBER]
SET [TABLE1].NAME = [TABLE2].NAME

 

[CJBIRKIN]

Sorry for the delay couldn't get back into the site yesterday.

Anyway;

Hi all,
I want to update the columns of one table to the columns of another table based on a common field.
This is what I tried but it's not working:

So i assumed you'd tried to run the query and it had given you an error message.

I created a quick DB and used the query builder to get the following Sql which is identical to yours;

UPDATE TABLE1 INNER JOIN TABLE2 ON TABLE1.ID = TABLE2.CARD_NUMBER SET TABLE1.Name = [TABLE2].[NAME];

And it worked fine.

So what is the problem??


Chris

 

[geo__]

As I said i figured it out

Hi,

If you notice my second post you will see that I figured out what the problem was on my first post, and I mentioned the solution on the second. So the second is correct. The first one not.

Anyway, thnx for your interest.

George

 

[___]

Re: As I said i figured it out

If you notice my second post you will see that I figured out what the problem was on my first post, and I mentioned the solution on the second. So the second is correct. The first one not.

 

[CJBIRKIN]

Glad that clears that up then!

 

[geo__]

sorry for the trouble i put you.

I should have been more clear.

George