Results 1 to 9 of 9

Thread: Simple Update?

  1. #1
    Join Date
    Nov 2002
    Posts
    6

    Question Unanswered: Simple Update?

    Hi,

    Forgive me but I think my TSQL is now a little rusty!

    Can anybody tell me if the following is possible, and if it is whats the correct syntax??? (I'm stuck with an ambiguous eContent column!)

    UPDATE eAttachment
    SET eContent = eAttachment2.eContent
    FROM eAttachment eAttachment1,
    eAttachment eAttachment2
    WHERE eAttachment1.eKey = 1
    AND eAttachment2.eKey = 2

    i.e Copy the contents of one field to another record in the same database without using a subquery? I would normal use a subquery but the field being coppied is of the data type Image which is causing problems.

    Cheers for the help,

    Paul.

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    probably...

    Code:
    UPDATE eAttachment1
       SET eContent = eAttachment2.eContent
      FROM eAttachment eAttachment1,eAttachment eAttachment2
     WHERE eAttachment1.eKey = 1
       AND eAttachment2.eKey = 2
    Have you produced a cartesion product in the join?
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Nov 2002
    Posts
    6

    Question

    If my understanding of cartesion products is correct this means I'd be returning all possible combinations for the join?

    This should not be so as the eKey field on the table is unique?

    Is this the answer to your question?

    Cheers for the help,

    Paul.

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    based on your where clause you will join every record who's eKey = 1 to every record who's eKey = 2, that would be a cartesion product.
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Nov 2002
    Posts
    6
    Originally posted by Paul Young
    based on your where clause you will join every record who's eKey = 1 to every record who's eKey = 2, that would be a cartesion product.
    But for my example wouldn't that be OK as only one record will ever have a key of 1 and one will have the ekey of 2 as this is unique. So The situation should be....

    eAtachment1.ekey eAttachment.eContents
    1 ABCD
    eAtachment1.ekey eAttachment.eContents
    2 EFGH

    And I need the final outcome to be...
    eAtachment1.ekey eAttachment.eContents
    1 EFGH
    eAtachment1.ekey eAttachment.eContents
    2 EFGH

    Thanks again for the advice.

    Cheers

    Paul.

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    oops! sorry, didn't read the big about unique ids.
    Paul Young
    (Knowledge is power! Get some!)

  7. #7
    Join Date
    Nov 2002
    Posts
    6

    Question

    No problem, does that mean we're out of ideas?

    Thanks anyway,

    Paul.

  8. #8
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    what happened when you ran the corrected update statment?
    Paul Young
    (Knowledge is power! Get some!)

  9. #9
    Join Date
    Nov 2002
    Posts
    6

    Talking

    Woops!

    My Mistake - I hadn't realised you'd updated the code! Shows its too late for work here in the UK! Thanks the example works a treat, my problem was that I needed to reference the alias!.

    Once again many thanks, gonna clock off for the night now!

    :-)

    Cheers

    Paul.

Posting Permissions

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