CODEWITHSUNDEEP
sql-server
robert woods
robert woods

Reputation: 375

How do I run a sql update on the results of a query

WITH A   -- Get a list of unique combinations of Shipmentnumber and ShipmentType
AS  (
   SELECT Distinct
          f_Shipmentnumber
     ,    f_ShipmentType
   FROM  t_shipment
)
,   B  -- Get a list of all those shipmentnumbers values that have more than one shipmentType associated
AS  (
    SELECT f_Shipmentnumber
    FROM   A
    GROUP BY
           f_Shipmentnumber
    HAVING COUNT(*) > 1
)
SELECT 
         A.f_Shipmentnumber
    ,    A.f_ShipmentType
FROM    A
    JOIN B
        ON  A.f_Shipmentnumber = B.f_Shipmentnumber

This is what I have and it returns beautifully however I need to set shipmentType=1 where shipmentTyp=2 for just these results.

Upvotes: 0

Views: 53

Answers (2)

Dmitriy Khaykin
Dmitriy Khaykin

Reputation: 5258

You can use another CTE for that last select, and then just use an update statement:

WITH A   -- Get a list of unique combinations of Shipmentnumber and ShipmentType
AS  (
   SELECT Distinct
          f_Shipmentnumber
     ,    f_ShipmentType
   FROM  t_shipment
)
,   B  -- Get a list of all those shipmentnumbers values that have more than one shipmentType associated
AS  (
    SELECT f_Shipmentnumber
    FROM   A
    GROUP BY
           f_Shipmentnumber
    HAVING COUNT(*) > 1
), C -- This CTE will be the one for your update statement, assuming that f_shipmentnumber is a primary key
AS ( 
    SELECT 
             A.f_Shipmentnumber
        ,    A.f_ShipmentType
    FROM    A
        JOIN B
            ON  A.f_Shipmentnumber = B.f_Shipmentnumber
)
UPDATE t_shipment
SET f_ShipmentType = 1
FROM C as cte
   JOIN t_shipment s ON cte.f_shipmentnumber = s.f_shipmentnumber
WHERE cte.f_shipmenttype = 2

Note that the update statement simply joins the final CTE results by f_shipmentnumber, and the update will only update the records that are returned in that final select statement, now wrapped in a CTE.

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

if you want change 1 for 2 just use a CASE

SELECT 
         CASE WHEN A.f_Shipmentnumber = 2
              THEN 1
              ELSE A.f_Shipmentnumber
         END as f_Shipmentnumber
    ,    A.f_ShipmentType
FROM    A
    JOIN B
        ON  A.f_Shipmentnumber = B.f_Shipmentnumber

Upvotes: 0

Related Questions