How do I update a table from a SELECT statement in SSMS 2012?

Question

I have a table that I would like to update based on the results of a query. The table that I want updated is called dbo.CSL. The field I want to update is called dbo.CSL.PartType. How can I update this field based on the results of this query?

SELECT
    b.Part,
    b.PartCreated,
    b.LastSalesDate,
    CASE WHEN b.LastSalesDate < (getdate()-365) OR b.LastSalesDate IS NULL AND b.PartCreated <= '2013-12-31' THEN 'C'
        WHEN b.LastSalesDate < (getdate()-365) OR b.LastSalesDate IS NULL AND b.PartCreated >= '2014-01-01' THEN 'N'
    ELSE 'D'
    END AS PartType
FROM
(SELECT
    a.Part,
    a.PartCreated,
    MAX(s.SBINDT) AS LastSalesDate
FROM
    dbo.SalesData s
    RIGHT JOIN
(SELECT
    c.Part,
    c.PartCreated
FROM
    dbo.NewCSL c
) AS a
ON a.Part = s.SBITEM AND s.SBTYPE = 'O'
GROUP BY
    a.Part,
    a.PartCreated
) AS b
ORDER BY
    LastSalesDate

First of all, remove the `order by` Then say what is the new value for **PartType** — vaheeds, Aug 24 '15 at 20:17

score 0 · Answer 1 · edited Aug 24 '15 at 20:35

0

See this:

UPDATE dbo.CSL 
SET dbo.CSL.PartType = (SELECT TOP 1 
                        FROM MyTable 
                        WHERE TableX = ConditionX) 
WHERE Field1 = Condition1

You have to change your statement to return only one row and one column, and then set it to the update statement, or instead concatenate the info.

Cheers.

edited Aug 24 '15 at 20:35

marc_s

732,580
175
1,330
1,459

answered Aug 24 '15 at 20:26

WltrRpo

263
2
13

That's not at all what I am trying to do. Each table contains the same field, Part. I want to link on the Part field and update the PartType field from the query results. – tsqln00b Aug 24 '15 at 20:33

How do I update a table from a SELECT statement in SSMS 2012?

1 Answers1