I have a Hold table where we load customer information (id#, address, etc) from a text file and would like to load that data into our Master table.

My problem is this.

1. The column names are different in the two tables.
2. The Master table has extra columns and are in a different order than the Hold table.


I can see using the UPDATE command, but how do I make sure the column data in the Hold table gets loaded into the right column data in the Master since they are in a different order ?

Would I use the WHERE command and specify COLUMN A in the Hold table equals COLUMN B in the Master ?

Thanks in advance from what probably is a novice question.

which database?

It's an Informix SQL database.

that would've been my first guess

the reason i ask is that there isn't a standard sql way to do this, and this is the standard sql forum

therefore, let's move this thread to the informix forum and see what we get

There isn't a lot of detail about the tables involved, but you can test something like this to see if it will get you what you need:

UPDATE mastertbl SET
mstr_addr = (SELECT addr FROM holdtbl WHERE id = mstr_no),
mstr_city = (SELECT city FROM holdtbl WHERE id = mstr_no)
WHERE mstr_no IN (SELECT id FROM holdtbl);

I believe this covers most of your concerns (i.e. different column names, the order of the columns does not matter, the number of columns may differ between the two tables).

HTH.

Thank you. I appreciate the response and will try it out.

It worked ! Now I know what to do if I run into a similiar situation. Thank you for your help.