Using LIKE comparison with update queries

Anyone know if it's possible to update records in a MS SQL table using the LIKE comparison in conjunction with records from another table.

For example, in one table I have a list of company names together with a client number, similar data is held in another table, same fields, but the company names are slightly different (e.g. ABC Limited / ABC Ltd) in some way. I want to be able to update the client number in one table (currently null) with the client number from the other table using a LIKE comparison on the company names.

In simple terms, I want the following "dummy" syntax to work:

UPDATE ...
SET ....
FROM ...
WHERE [CompanyName1] LIKE [CompanyName2]

This works if a use a string value (e.g. 'ABC Lim%') or if I use the equals comparison instead, but I want to perform the update where records are similar.

Any clues??

You would need to extract a portion of the company name. If you ran a sql update statement like this

It wouldn't update anything, because there is not record in table2 with a company name like that.

you could do it like this

Which would update any record where the Company name begins with "ABC".

I've got a bit more complicated scenario

I got two tables, comments, and companies, now I want to update the subscribers fields in the comments table, if the company exists in both the comments table, as well as the companies table

How would I do this?

I tried "UPDATE comments SET subscribers = 'Y' WHERE supplier = companies.company_name"

But this doesn't seem to work?

You would have to hold the company name in a variable.
Or
Do a larger sql statment that actually grabs the company name as well.