Sql updating table from another table
The second has had some typos fixed throughout as well... Without the primary key, fixing the data with an update statement is likely to get very messy.
You're most likely better off getting a fresh export of the original data with the PK included, run the process/es that you did to clean up the data, then run an update statement.
And then put constraints on the table to prevent the errors from occurring again.
The update statement should be fairly simple though.
I have a database in Access that I am trying to fix (actually, I'm working on a copy of the original).
There are two tables, one with data input errors, and a second that is a copy of those errors, but fixed, if that makes sense.
Hi Tom, Due to migration to new system we have to change all our account numbers. June 28, 2005 - pm UTC yes, the predicate in the join for the fictional question without a sample table and data was wrong (everytime I wing it, try to answer WITHOUT TESTING, it comes out wrong. test cases are relevant :) Hi Tom, Thanks for answering my question. I think there could be some errors in records contained in that database. It worked on 2 of my databases, but didn't work on 3rd Database.( Client number is consist of branch, Number, Sub Number Currency Code ) We have one big transaction table around 1 million records, having many columns, and at many places client numbers are stored from account master under various columns 1. You told me to use update ( select old.old_account_number, new.new_account_number from old_table old, mapping_table new where old.old_account_number = new.old_account_number ) set old_account_number = new_account_number; But I think my questions still reamin unanswered 1. In your answer only one number say client number will get updated. I want to write SQL ( not PL/SQL ) to update transaction table from mapping table at one stroke. regards & thanks pjp update ( select old.old_account_number, new.new_account_number from old_table old, mapping_table new where old.old_account_number = new.new_account_number ) set old_account_number = new_account_number; disabling foreign keys during the operation and enabling them afterward. update ( select old.old_account_number, new.new_account_number from old_table old, mapping_table new where old.old_account_number = new.old_account_number ) set old_account_number = new_account_number; (ie in the subquery, match the old account numbers not try matching the old in the old table to the new in the mapping table) Of course if the original posting had the CREATEs/INSERTS etc, it would have a quick and east test.