Batch UPDATE with joined files.

[Guest.Visitor]

I'm trying to copy all the fields from one file over the fields in another file where the unique keys are the same. I'm trying to do this in one shot with SQL. eg. UPDATE ORDERRPF SET ORDNME = VCANME, ORDAD1 = VCAAD1, etc. FROM ORDERRPF, VCAINF WHERE ORDORD = VCAORD HELLLPPP!!!

[Guest.Visitor]

This has been hammered out several times on this forum; what you're trying to do is not 'relational'. One way is to create a third table consisting of the columns in file_a plus columns containing the update values from file_B. Update this table then copy to the original with *MAP *DROP. bobh

[Guest.Visitor]

Thanx Bob, So you mean there is no magic SQL statement for this type of problem? I ended up doing something simliar to what you described. I created a view of the combined tables joined by unique key and copied this into a new file. I then queried the matching records from the original table to pull them into a save file. I then ran a similar query to delete these records from the original. Finally I wrote a query to insert the records from the new joined file into the original. (I overlooked CPYF *MAP *DROP.) Are you sure there is no better way to do what I'm doing? Can you explain why my original idea is not relational? Thanx for your help.

[Guest.Visitor]

I'm not sure whether a better way exists, but I am sure that I don't know it if it does. I'll have to get back to you for info on relational and set theory. bobh

[Guest.Visitor]

Thank you for all of your help. I anticipate your response.

[Guest.Visitor]

Clifton, Yes, you can update data in one table with data from another using SQL. BUT, there are constraints to it. You have to make sure that you return no less than one and no more than one record for an update to occur (if the field allows null values, you could return zero records and null would be the updated value). Do a search of the forums for "update and sql and another" for several entries on this subject. To see one somewhat difficult statement that worked, go here: http://forums.midrangecomputing.com/MCForums.nsf/afc6f463929b6359882567b8001d0b6 6/FC645712FE207A6F882569E700643E2B?OpenDocument Bill

[Guest.Visitor]

Thanx Bill, This is just what I was looking for. I have yet to try any of these methods but I will as time allows. I may be back with more questions. I apologize for my late response. It's been busy here.