Change column value after left join SQL

Question

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.

HOW did you try to use update? And why do you need the distinct? — derpirscher, Feb 08 '21 at 16:07
Does this answer your question? [How can I do an UPDATE statement with JOIN in SQL Server?](https://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql-server) — derpirscher, Feb 08 '21 at 16:10
Please read [this](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. — HABO, Feb 08 '21 at 21:06

score 1 · Accepted Answer · answered Feb 08 '21 at 17:13

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

Change column value after left join SQL

1 Answers1