• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

a very basic question on sql...

 
Amit Mathur
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can we UPDATE more than one table using a single SQL statement?
 
Jayesh Lalwani
Ranch Hand
Posts: 502
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 381
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34965
378
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Amit Mathur
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Amit Mathur
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 232
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic