Updating data from one table to another
If the user has update permission on table A, but only has select permission on table B, they cannot update via the first example. I tried the MERGE command and it is 10 fold fast, however i have few questions. I need to update 1 column (number) with a column from another table (around 350,000 records) based on a join value in the 2 tables. Can you think of a good way to do this in as little time as possible?
Oracle will return ORA-01031 (insufficient privileges). Hi Tom, I have a related question for an UPDATE - that takes unexpected long time. You said to use Oracle9I Merge statement when inserting or updating the data from a stagging table to history table. In the first table (the 5,000,000 record table) all the records will be updated. (I'll be happy with anything under 20 minutes)Thanks in advance.
The Ask TOM team will be busy at Open World this month. I don't understand what's the problem.i am going to give u full overview of my problem. The software is available in different portion of the country for data entry and report generation etc. Now, you "two step" it: insert into gttselect b.id, count(*) cnt from tabb b, taba a where = and a.cycle = b.cycle and b.site_id = 44 and b.rel_cd in ( 'code1', 'code2', 'code3' ) and b.groupid = '123' and is null group by b.id/that gets all of the id/cnts for only the rows of interest.Consequently, to prevent this, you might do this: One potential issue may arise when the subquery actually finds more than one matching row in tbl B.If this happens, the UPDATE will terminate with an error ("subquery may return only one row"). The exact SQL that is required depends on which database system you're using, but here's an example for SQL Server: Read an example of an SQL case expression from our SQL expert Rudy Limeback.When the subquery fails to find a matching row in tbl B, the subquery returns NULL.But since the UPDATE has no WHERE clause, it will update all rows, even those where the subquery returns NULL.