modify select query to update query

• paul-144416

  SSC Enthusiast

  Points: 141

  More actions

  May 18, 2004 at 1:33 am

  #179646

  hi ,

  I would like to change the following query into an update query where categ would be the field to update:

  SELECT  categ = CASE WHEN  DERIVEDTBL.TOTAL_VALUES = 0 THEN 'cat0' WHEN F.VALUES / DERIVEDTBL.TOTAL_VALUES < 0.1 THEN

                         'cat1' WHEN F.VALUES / DERIVEDTBL.TOTAL_VALUES < 0.2 THEN 'cat2' WHEN F.VALUES / DERIVEDTBL.TOTAL_VALUES < 0.4 THEN 'cat3'

                         WHEN F.VALUES / DERIVEDTBL.TOTAL_VALUES < 0.6 THEN 'cat4' ELSE 'cat5' END

  FROM         dbo.sales F INNER JOIN

                            (SELECT     DERIVEDTBL.PERIOD, DERIVEDTBL.REP, SUM(VALUES) AS TOTAL_VALUES

                              FROM          dbo.sales DERIVEDTBL

                              GROUP BY PERIOD, REP) DERIVEDTBL ON F.PERIOD = DERIVEDTBL.PERIOD AND F.REP = DERIVEDTBL.REP

  GO 

   

  any ideas ?

   

  thanks

• lucky-80472

  SSCertifiable

  Points: 7793

  More actions

  May 18, 2004 at 2:49 am

  #506749

  Hi Paul,

  Try this...

  UPDATE DBO.SALES F

  SET F.CATEG = CASE

      WHEN DERIVEDTBL.TOTAL_VALUES = 0 THEN 'cat0'

      WHEN (F.[VALUES] / DERIVEDTBL.TOTAL_VALUES < 0.1) THEN 'cat1'

      WHEN (F.[VALUES] / DERIVEDTBL.TOTAL_VALUES < 0.2) THEN 'cat2'

      WHEN (F.[VALUES] / DERIVEDTBL.TOTAL_VALUES < 0.4) THEN 'cat3'

      WHEN (F.[VALUES] / DERIVEDTBL.TOTAL_VALUES < 0.6) THEN 'cat4'

      ELSE 'cat5'

     END

  FROM DBO.SALES F

   INNER JOIN (SELECT DERIVEDTBL.PERIOD, DERIVEDTBL.REP, SUM(VALUES) AS TOTAL_VALUES

     FROM DBO.SALES DERIVEDTBL

     GROUP BY PERIOD, REP) DERIVEDTBL ON F.PERIOD = DERIVEDTBL.PERIOD AND F.REP = DERIVEDTBL.REP

  It is only a trail.

  
  Lucky

• paul-144416

  SSC Enthusiast

  Points: 141

  More actions

  May 18, 2004 at 5:23 am

  #506757

  only a trail but a happy trail !

   

  thanks for your help