Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2004
    Posts
    5

    Question Unanswered: How do I update records in one table based on values in another table ?

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which database?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2004
    Posts
    5
    It's an Informix SQL database.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2005
    Posts
    43
    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.

  6. #6
    Join Date
    Dec 2004
    Posts
    5

    Thumbs up

    Quote Originally Posted by June C. Hunt
    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.


    Quote Originally Posted by r937
    that would've been my first guess

  7. #7
    Join Date
    Dec 2004
    Posts
    5

    Thumbs up

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •