aspose file tools*
The moose likes JDBC and the fly likes a very basic question on sql... Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "a very basic question on sql..." Watch "a very basic question on sql..." New topic
Author

a very basic question on sql...

Amit Mathur
Ranch Hand

Joined: Jan 22, 2002
Posts: 49
Can we UPDATE more than one table using a single SQL statement?


amit
Jayesh Lalwani
Ranch Hand

Joined: Nov 05, 2004
Posts: 502
No. Although you can update multiple tables using seperate SQL statements within the same transaction. All the updates will be commited only when you commit the transaction
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
There are really two answers to this question. Yes and no.

Yes it is possible. Basically you do a join and can update multiple tables based on that.

No because not all databases support that. Further, you must be doing a JOIN to make this work (you can't update un-related tables at the same time) and last because quite frankly I'm not sure it's a very good idea anyway. I think generally speaking this is a case where the potential risks outweigh the rewards.

I believe that if you would like to have a more specific answer I think it would be most helpful if you described more of what problem you are trying to solve. Perhaps for example cascading foreign keys would help you.
Amit Mathur
Ranch Hand

Joined: Jan 22, 2002
Posts: 49
Ok ..well the database is Oracle 9i.
And the scenario is like this - we have data for customers in more than one table. And a few of the tables have same PK (say CustID).
Now for a given CustID I want to update data in all those tables.

If this is a case of database design being improper then also pls let me know. Thanks!
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30506
    
150

Amit,
Whether the database design is sound depends on what is in those tables. For example, it would be valid to have a table for orders and another one for currently available offers. So the customerid is a foreign key (in addition to being a primary key.)

What is your reason for wanting only one SQL statement? If it is network time, you could use a batch update to do a bunch of queries in one trip.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Amit Mathur
Ranch Hand

Joined: Jan 22, 2002
Posts: 49
The only reason I wanted to know for updates in a single query was out of curiosity - I wanted to know if there was a way possible.
Actually we are not facing any issues bcos of that...and yeah we will use the batch update only Thanks.
Amit Mathur
Ranch Hand

Joined: Jan 22, 2002
Posts: 49
There is one more clarification I would need - suppose for a given CustID I have about 70 fields. Is is okay to design a table having 70 columns?
Can we split the table into 2 tables of 35 columns each? Which approach is better? (All 70 fields are going to be fetched/updated together)
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

That depends entirely on what the entity you are modelling is. If you normalize your model and discover you have an entity with 70 attributes, it is entirely possible that this entity is completely valid.

However, it is generally not a good idea to arbitrarily split a table into two if you become worried about how many columns your table has. The limit on the number of columns a table can have varies from database to database. If you are hitting the limit, review your model, since unless you are modelling fairly unusual entities you probably should not come anywhere near this limit.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Amit Mathur
Ranch Hand

Joined: Jan 22, 2002
Posts: 49
Well the entity is normalized. And the reason as you said was just to split a table into two bcos the number of columns appeared to be large. However since oracle supports ~70 columns I think we will keep it as a single table only. Thanks!
Sripathi Krishnamurthy
Ranch Hand

Joined: Mar 07, 2005
Posts: 232
Originally posted by Amit Mathur:
Well the entity is normalized. And the reason as you said was just to split a table into two bcos the number of columns appeared to be large. However since oracle supports ~70 columns I think we will keep it as a single table only. Thanks!



Amit, Correction for ~70 columns.
Oracle8 supports 1000 columns for a table.Not sure about 9i.
Normally the columns in the table depends on the page size. larger the page size, more number of columns can be accomodated.
Amit Mathur
Ranch Hand

Joined: Jan 22, 2002
Posts: 49
Originally posted by Sripathi Krishnamurthy:



Amit, Correction for ~70 columns.
Oracle8 supports 1000 columns for a table.Not sure about 9i.
Normally the columns in the table depends on the page size. larger the page size, more number of columns can be accomodated.


Thanks Sri..
What I meant to say was Oracle will support my table with 70 columns.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: a very basic question on sql...