问题

I have a table called Table1 that looks something like this:

MEID  Type
KELO  mb
OLEM  mb-sd

And another table called Table2:

MEID  SD
KELO  NO
OLEM  YES

I'm updating Table2 SD column like this:

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)

I'm just wondering how can I update Table2 SD column if the ID is no longer "mb-sd" in Table1 the next day.

For example:

MEID  Type
KELO  mb
OLEM  mb

MEID   SD
KELO  NO
OLEM  NO

It sounds easy but I still couldn't figure it out yet, so any help would be really appreciated.

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

答案1

得分: 2

首先，正如Panagiotis Kanavos所提到的，在这里你的设计是非规范化的。与其Type具有'mb-sd'这样的值，你应该有两行分别带有'mb'和'sd'的值。

正如我在评论中提到的，这不是你应该存储在数据库中的东西；使用一个VIEW（或者可能是参数化的内联表值函数）来处理这样的情况。在一个表中存储每当另一个表中的值发生变化时都需要更改的数据几乎总是一个坏主意；通常涉及到会很快变得昂贵的TRIGGERs。如果你使用类似VIEW这样的东西，那么数据只需要在你查询时确定，而无需不断地对你的表进行UPDATE。

由于我们这里只有两个表，后者似乎与第一个表相同，只是多了一个yes/no，我将转换为一个表。我还展示了如何查看表中是否都出现了md或sd，因为我怀疑这是一个后续问题。

然而，我也会对你的数据进行规范化，这是你应该做的事情：

--创建示例表
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;

db<>fiddle

<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;

db<>fiddle