MEID  Type
KELO  mb
OLEM  mb-sd

MEID  SD
KELO  NO
OLEM  YES

UPDATE Table2 
SET SD = (CASE 
              WHEN EXISTS (SELECT 1 
                           FROM Table1 
                           WHERE Table1.MEID = Table2.MEID AND Table1.[Type] LIKE '%sd%') 
                  THEN 'YES'  
                  ELSE 'NO' 
          END)

MEID  Type
KELO  mb
OLEM  mb

MEID   SD
KELO  NO
OLEM  NO

--创建示例表
CREATE TABLE dbo.YourTable (MEID varchar(4),
                            Type varchar(2));
--示例数据
INSERT INTO dbo.YourTable (MEID, Type)
VALUES('KELO','sd'),
      ('OLEM','md'),
      ('OLEM','sd');
GO
--创建视图
CREATE VIEW dbo.YourView AS
    SELECT MEID,
           CASE COUNT(CASE Type WHEN 'md' THEN 1 END) WHEN 0 THEN 'No' ELSE 'Yes' END AS md,
           CASE COUNT(CASE Type WHEN 'sd' THEN 1 END) WHEN 0 THEN 'No' ELSE 'Yes' END AS sd
    FROM dbo.YourTable
    GROUP BY MEID;
GO
--查询视图
SELECT MEID,
       sd,
       md
FROM dbo.YourView;

<details>
<summary>英文:</summary>
Firstly, as [mentioned](https://stackoverflow.com/questions/76220049/how-to-update-sql-server-database-column#comment134412474_76220049) by [Panagiotis Kanavos](https://stackoverflow.com/users/134204/panagiotis-kanavos), it appears that your design is denormalised here. Instead of `Type` having a value like `&#39;mb-sd&#39;`, you should have 2 rows with the values `&#39;mb&#39;` and `&#39;sd&#39;`.
As I [mentioned](https://stackoverflow.com/questions/76220049/how-to-update-sql-server-database-column#comment134412418_76220049) in the comments, this isn&#39;t something you should be storing in your database; use a `VIEW` (or perhaps a parametrised inline table value function) for something like this. Storing data in a table that needs to change every time a value in *another* table changes is almost always a bad idea; it normally involves `TRIGGER`s which can get expensive quickly. if you use something like a `VIEW` then the data only needs to be determined at the time you query that, and there&#39;s no need to constantly `UPDATE` your table.
As we only have two tables here, and latter appears to be the same table as the first with a yes/no, I&#39;m going to switch to a single table. I also demonstrate how you could see if both `md` or `sd` appear in the table, as I suspect that is a follow on question.
I also, however, normallise your data, which is something you should be doing:
```sql
--Create sample table
CREATE TABLE dbo.YourTable (MEID varchar(4),
                            Type varchar(2));
--Sample data
INSERT INTO dbo.YourTable (MEID, Type)
VALUES(&#39;KELO&#39;,&#39;sd&#39;),
      (&#39;OLEM&#39;,&#39;md&#39;),
      (&#39;OLEM&#39;,&#39;sd&#39;);
GO
--Create View
CREATE VIEW dbo.YourView AS
    SELECT MEID,
           CASE COUNT(CASE Type WHEN &#39;md&#39; THEN 1 END) WHEN 0 THEN &#39;No&#39; ELSE &#39;Yes&#39; END AS md,
           CASE COUNT(CASE Type WHEN &#39;sd&#39; THEN 1 END) WHEN 0 THEN &#39;No&#39; ELSE &#39;Yes&#39; END AS sd
    FROM dbo.YourTable
    GROUP BY MEID;
GO
--Query view
SELECT MEID,
       sd,
       md
FROM dbo.YourView;