In my Oracle database, I have 2 tables info & profile. The profile table has the columns city & state. I want to add the these columns to the info table & also populate these columns in the info table with the same data as in profile table. I have added the columns. But I am not able to copy the data in these columns from the profile table to the info table. Couls someone please help? Its really very urgent.
hi Rebecca Abraham You can take a look with the following example that copy from oracle website
Inserting Values with a Subquery: Example The following statement copies employees whose commission exceeds 25% of their salary into the bonuses table (which is created in "Merging into a Table: Example"):
Rebecca, is there already data in the info table. If so, what they posted will not work, it will add all new records for those in the profile table.
What you need is there to be a link between the two tables so that you can perform an Update statement. If there is no link, then you won't be able to do a join that will create the data the way you would like it.
Julian is pretty close, but made it an insert statement instead of an update statement.
I haven't written Oracle SQL in about 5 months, so I don't remember if you can set two columns like that or if you need the subquery twice, once for each field.
Here's the other way just in case, but it would take longer.
I haven't written Oracle SQL for over 2 years, but that's no excuse for getting this wrong! Thanks Mark for pointing it out and, apologies to Rebecca if that just made your headache worse!
I an attempt to partially redeem myself I'll say that I'm pretty sure you can't use the syntax in the first example but you can combine the other two as follows:
Looks horrible, doesn't it? Unfortunately this is the ANSI standard syntax and the only way that Oracle provides for doing this kind of thing.
Other RDBMS such as Sybase & MS SQL Server add a FROM clause to the UPDATE syntax, which greatly simplifies this kind of statement. MySQL allows you to specifiy more than one table in the UPDATE clause; the first being the one that is updated. Unfortunately the above method is the only portable way of doing it (and even then I'm not sure if it'll work in MySQL...)
Hope that's useful/interesting.
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop