Multi Table Update

[fastedge]

Howdy folks. I need to make updates to several tables at one time. Conventionally I would create several sql update statements hitting the DB every time. I find this a bit taxing on the DB server (maybe it's not) and would like to update the various tables with just one hit.

Is there a way to do this?

P.S. - My pages are in CF5. Not that I think this would make much of a difference since it's really a SQL question.

Looking forward to your ideas.

Thanks,
Ed Mercado

[paulhiles]

Hi Ed,

While I don't have the answer to your question, I thought I'd get the ball rolling so to speak. Wouldn't you need to create a JOIN or a VIEW first of all between the different tables before you update the necessary columns? There would also need to be a WHERE condition and ideally related primary or foreign keys. What database are you using? is it SQL Server, MySQL or Access?

For SQL Server this "view" update seems cool...
http://www.informit.com/articles/article.asp?p=130855&seqNum=4

For MySQL, this looks pretty promising....
http://dev.mysql.com/doc/mysql/en/UPDATE.html

I didn't find anything on Access, but the SQL Design view is usually pretty useful at testing out queries.

To give you a improved chance at getting a more specific response, I'm moving this post to Database Talk, where hopefully you'll get the help you're after!

Good luck to you Ed.. I'll be watching this thread to find out the answer myself! :o)

Paul

[fastedge]

Thanks Paul. I guess I should have mentioned the DB is Access.

I know that with JOIN I can pull data from multiple tables, but it's not for updateing as far a I know.

Thanks,
Ed