DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: UPDATE from other table

  1. #1
    Join Date
    Nov 2001
    Posts
    8

    Exclamation UPDATE FROM OTHER TABLE

    Hi,

    I would like to update some records in table ORG from table TEMP.
    All I can come up with is this:

    UPDATE ORG
    SET ORG.FIELD1 = (SELECT T.FIELD1
    FROM ORG O, TEMP T
    WHERE O.FIELD1=T.FIELD1)

    But I get the message, that no unique record is returned.

    How can I alter this sql-statement, so that I can update the record that is currently being accessed?

    Am I being clear enough, or does this sound 'illogical captain'?

    Thanx.

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Do you have a unique constraint on ORG.FIELD1?



  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by welrogie
    UPDATE ORG
    SET ORG.FIELD1 = (SELECT T.FIELD1
    FROM ORG O, TEMP T
    WHERE O.FIELD1=T.FIELD1)
    Change that to:

    UPDATE ORG O
    SET O.FIELD1 = (SELECT T.FIELD1
    FROM TEMP T
    WHERE O.FIELD1=T.FIELD1);

    This will update *all* your records in ORG - if you want to select only *some* of the records (as you say in your post) you must add some WHERE condition in the UDATE statemnet...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Nov 2001
    Posts
    8

    constraint ORG.FIELD1

    Yes, I've got a unique constraint on ORG.FIELD1.


  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Can you give us some sample data of ORG and TEMP and explain what you are trying to achieve?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Nov 2001
    Posts
    8

    Exclamation sample data

    The table ORG contains the following data:
    CLIENTID, NAME, CITY, DEPARTMENT

    An example:
    001 name1 city1 department1
    002 name2 city2 department2

    The table TEMP contains the same data:
    CLIENTID, NAME, CITY, DEPARTMENT

    An example:
    001 name1 city9 department9
    002 name2 city9 department9
    003 name3 city3 department3
    004 name4 city4 departmen4

    What I'm trying to do is the following:
    The TEMP table contains different information about clients than the ORG table. Whenever a client from the TEMP table already exists in the ORG table, the record should be updated in the ORG table. And if a client from the TEMP table does not exist in the ORG table, the record should be added.

    In this example that means that clients 001 and 002 must be updated with city9 and department9 in the ORG table.
    And clients 003 and 004 will have to be added in the ORG table.

    Is this clear enough? PLEAZ HELP ME!

  7. #7
    Join Date
    Nov 2001
    Posts
    8

    Exclamation COMPARE TABLES

    I've added information as above. Please HELP!!!!!

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    UPDATE org o SET o.city, o.department =
    (SELECT city, department FROM temp WHERE clientid = o.clientid)
    WHERE EXISTS SELECT null FROM temp WHERE clientid = o.clientid;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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


Click Here to Expand Forum to Full Width