Results 1 to 4 of 4

Thread: Update query

  1. #1
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    44

    Unanswered: Update query

    can someone tell me what is wrong with this update query

    UPDATE tblprocessInfo
    SET ProgDate = (
    SELECT TermInformation.ProgDate
    FROM tblProcessInfo, TermInformation
    WHERE TermInformation.Term = tblProcessInfo.Term AND TermInformation.SID = tblProcessInfo.SID);


    Basically, both tables processInfo and termInformation are similar, except terminformation has extra field "ProgDate".
    So what I did, i created that field in tblProcessInfo to import from termInformation.ProgDate.
    Now of course the processInfo.progdate is empty and termInformation.Progdate has the stuff...
    So I created the query as you can see, but doesnt work..

    any solutions or ideas?!

    thanks

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Update tableA ta
    set column =
    (select column from tableB where key = ta.key)
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    While your query as written makes logical sense, it will cause consternation for most database engines (MS-SQL included). The problem is that you've used the same table alias twice in a single context, but most optimizers won't realize that, so they'll generate a Cartesian join! You can probably simplify the query down to:
    Code:
    UPDATE tblprocessInfo 
       SET ProgDate = (
          SELECT TermInformation.ProgDate
          FROM TermInformation
             WHERE TermInformation.Term = tblProcessInfo.Term
                AND TermInformation.SID = tblProcessInfo.SID);
    This only uses the tblprocesInfo alias once, and it should prevent the confusion.

    -PatP

  4. #4
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    44

    hmm

    thanks..
    that makes sense...

    I will try it later on

Posting Permissions

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