An update function based on values in another table

[john434]

Hi

I'm currently administrating a large relational database.

I need to Run an update statement something like this.

UPDATE table1
SET column1 = '6c'
WHERE column2 = 602 AND column3 = 45;

The problem is that column2 resides in a different table that column1 and column3.

Are you with me? I hope so, ok.

I have tried the following as well.

UPDATE table1
SET column1 = '6c'
WHERE table2.primarykey = table1.primarykey AND table2.column2 = 602 AND table1.column3 = 45;

i've also tried loads of variations of the above.

Any help will be totally appreciated.

Thanks

 

[JonFer]

thread701-905099 has an example of an update where you join two tables. Make sure your tables are indexed, ideally with primary keys.

John

http://www.interautomateconsulting.com

 

[RickSpr]

John434, does JonFer's answer help you? It's not clear to me that you recognize the need to do a join of the tables.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[john434]

cheers,

The problem is really with how i've asked the question, sorry bout that.

The answer certainly helps however, it doesn't seem to run.

This is the code i've written:

UPDATE table1 INNER JOIN table2
ON table1.(primarykey) = table2.(primarykey)
SET table1.column1 = (value)
WHERE table2.column2 = (value)

This doesn't seem to work.

any ideas?

 

[RickSpr]

It looks all right to me. What does "This doesn't seem to work" mean? Does it give you an error? Does it run quietly but update no rows? Does it update the wrong rows?

Also, it's unusual (though not necessarily wrong) for two tables to have identical keys, but your JOIN indicates that they do. I may be reading into it too much.

If you don't mind, an actual cut-and-paste of your query would be better than a simplified version. It very often happens that people fix the bug they're having a problem with, when they simplify or even just retype (inaccurately) the code. That wastes everybody's time when it happens.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein