CODEWITHSUNDEEP
t-sql
Lucius
Lucius

Reputation: 134

Change column value after left join SQL

I have the following statement:

select distinct x_order.code , x_order.status , x_order.project , project.active , project.code as projectcode
from project
left join x_order on x_order.project = project.code
where status = 'B' and active = 0

Which gives me a nice table with all the records I want to change. I now need to change the values in the 'status' column from B to D in this table. How can I do that? I tried UPDATE, but to no success.

Upvotes: 0

Views: 474

Answers (1)

WAMLeslie
WAMLeslie

Reputation: 1261

Not sure of the exact table structures you're using, but would a CTE work for you like this...

;WITH CTE
AS (
    select distinct x_order.code as ordercode , x_order.status , x_order.project , project.active , project.code as projectcode
    from project
    left join x_order on x_order.project = project.code
    where status = 'B' and active = 0
    )
UPDATE X 
    SET [status] = 'D'
FROM x_order X
JOIN CTE C
    ON X.code = C.ordercode

Upvotes: 1

Related Questions