How to use not in / not like to compare values from different tables in SQL

I have SQL 2019 with these two tables – Orders and Customers with respective columns

id,order_comment,customer_id

in Orders and

id,customer_name

in Customers. Order_comment is ntext type. I need to update the order_comment with customer_name value. The update should be something like this:

update o set order_comment=concat(c.customer_name,';',o.order_comment)
from
orders o
left join customer c on c.id=o.customer_id
where o.id=1 and
convert(varchar(255),order_comment) not  in 
(select b.customer_name from customer b where o.customer_id=b.id)

If the customer_name is already part of the order_comment then the update should not happen. The above query works ok when the order_comment is empty. But if there is already text in the comment, it keeps updating the value. How should i change the query so when there is already text in the comment section, the update to happen only once?

Answer

There a lot of ways to achieve your goal and of course the best way depends on your setup and your further goals. In order to keep your update statement as most similar as possible to your attempt, you could do this:

UPDATE o SET order_comment=CONCAT(c.customer_name,';',o.order_comment)
FROM
orders o
LEFT JOIN customers c ON c.id=o.customer_id
WHERE o.order_comment IS NULL OR o.order_comment NOT LIKE CONCAT(c.customer_name,'%');

There are also other possibilites like subselects, not exists etc., the best idea is that you try what is the best for you. You can check this example here: fiddle