Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Correlated update query help

    I am trying to use this query to update records in stsc.sku where loc/item exist in the pac.SKUUPDATE table, but all the rows in stsc.sku are being updated. What am I doing wrong?

    Code:
    UPDATE stsc.sku s
       SET (s.p_abc, s.p_manualparameters, s.p_commenttext, s.p_workinglistname) =
              (SELECT x.p_abc, x.p_manualparameters, x.p_commenttext, x.p_workinglistname
                 FROM pac.SKUUPDATE x
                WHERE s.loc = x.loc AND s.item = x.item)

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    If you do NOT want to UPDATE all the rows, you'll require a WHERE clause on the UPDATE statement itself.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2005
    Posts
    220
    Like this?

    Code:
    UPDATE stsc.sku s
       SET (s.p_abc, s.p_manualparameters, s.p_commenttext, s.p_workinglistname) =
              (SELECT x.p_abc, x.p_manualparameters, x.p_commenttext, x.p_workinglistname
                 FROM pac.SKUUPDATE x
                WHERE s.loc = x.loc AND s.item = x.item)
    WHERE EXISTS (SELECT 1 FROM pac.SKUUPDATE x
    	  		 WHERE s.loc = x.loc AND s.item = x.item)

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    Looks right, in that the WHERE clauses match up. Did it work?
    --=cf

  5. #5
    Join Date
    Sep 2005
    Posts
    220
    my fault. It works.

Posting Permissions

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