Q:

SQL Update Multiple Rows value based on same row

We have a table that contains all of our products.  In here is a column called UNITMEASURE.  This column is populated with values such as "EA, PR, PK, YD, etc".  Occasionally a 1 will get populated into this column.  I would like to run an update statement to change the 1's to whatever the other values are.
For example:
PRODUCT  UNITMEASURE
1234     1
1234     1
1234     PR
1234     1
1234     1
4321     1
4321     1
4321     EA
4321     1
4321     1

I want to be able to update the UNITMEASURE for all lines of PRODUCT 1234 to PR and PRODUCT 4321 to EA (from 1) with one query, but am completely stumped.  There are over 8,000 products that this could happen to, so I would want to lookup the UNITMEASURE <> '1' and update from that for each product.

A:

Try this way:
update producttable
set UNITMEASURE = case WHEN PRODUCT = 1234
                       THEN = 'PR'
                       WHEN PRODUCT = 4321
                       THEN = 'EA'
                       ELSE = UNITMEASURE
                  END

for updated question
update producttable
set UNITMEASURE = PT2.UNITMEASURE
from  producttable PT
join (select UNITMEASURE, PRODUCT 
      from producttable
      UNITMEASURE <> 1) PT2 
  on PT.PRODUCT = PT2.PRODUCT
where PT.UNITMEASURE = 1

