Results 1 to 7 of 7

Thread: UPDATE help

  1. #1
    Join Date
    Jun 2008
    Posts
    29

    Unanswered: UPDATE help

    Hi all,

    I'm having trouble finding a satisfying solution to the following problem :

    I've got an Access database with table A (e_id, f_id, g_id, b_val) that holds some data, with a key on *(e_id, f_id, g_id).
    I want to update table A based on information from table Xtmp that is linked from a CSV file. Xtmp has exactly the same structure as A, ie. (e_id, f_id, g_id, b_val).

    Is there a way to UPDATE table A in bulk with information from table Xtmp ? Basically, all I need to update is the b_val based on (e_id, f_id, g_id).

    The idea of parsing each row from Xtmp using DAO and throwing in an UPDATE instruction seems like a waste of resource, as Xtmp and A can be quite large.

    Another idea would be :
    1) DELETE FROM A WHERE EXISTS (SELECT * FROM Xtmp WHERE A.e_id = Xtmp.e_id, A.f_id = Xtmp.f_id, A.g_id = Xtmp.g_id);
    2) INSERT INTO A SELECT * FROM Xtmp;
    but it is not really updating, is it ?

    If you've got something better, I'm all ears

    Thanks !

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    UPDATE table_a
    SET    b_val = csv.b_val
    FROM   table_a As [a]
     INNER
      JOIN xtmp As [csv]
        ON a.e_id = csv.e_id
       AND a.f_id = csv.f_id
       AND a.g_id = csv.g_id
    ??
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2008
    Posts
    29
    Well, I'd say "of course" !
    Actually I wasn't aware that you could UPDATE ... FROM tableX
    But that's good news if I can
    Last edited by rapht; 08-14-08 at 08:07.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by rapht
    Well, I'd say "of course" !
    Actually I wasn't aware that you could UPDATE ... FROM tableX
    But that's good news if I can
    ...perhaps this would be a good time to get a reasonable quality SQL primer then?
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2008
    Posts
    29
    Quote Originally Posted by healdem
    ...perhaps this would be a good time to get a reasonable quality SQL primer then?
    Actually, it's been high time for a long time, but time is scarce and I don't intend to become a professional developer...

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Guys, guys, guys, you've both forgotten something.
    >> <<

    I assume the query has worked OK for you rapht?
    George
    Home | Blog

  7. #7
    Join Date
    Jun 2008
    Posts
    29
    Worked indeed, thanks again
    And true, let's not be too prompt in being serious

Posting Permissions

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