SQL查询 - 添加新列如果两个现有列匹配

Question

我想将列添加到现有的表，即返回“不匹配”的两个查询：

select * from dbo.customer 
where cus_title IN ('MRS','DAME','SISTER','MISS','MADAME','LADY','MS') 
and cus_gender <> 'F' 

select * from pv_customer 
where cus_title IN ('FATHER','SIR','MR') 
and cus_gender <> 'M' 

，或者如果“匹配”没有从上面的两个查询中选择？

有人能够协助alter table查询吗？

非常感谢

Answer 1

你可以只添加列并运行更新语句是这样的：

ALTER TABLE <table_name> 
ADD COLUMN match_status VARCHAR(10) NULL; 

UPDATE <table_name> SET match_status = 'match'; 

假设这个表要更新具有外键的客户ID，你会再更新与一些非匹配的是这样的：

UPDATE <table_name> SET match_status = 'non-match' 
WHERE customerId IN (select customerId from dbo.customer where cus_title IN ('MRS','DAME','SISTER','MISS','MADAME','LADY','MS') and cus_gender <> 'F') 
OR customerId IN (select customerId from pv_customer where cus_title IN ('FATHER','SIR','MR') and cus_gender <> 'M'); 

Answer 2

ALTER TABLE [SomeTable] ADD COLUMN Check VARCHAR(10) 

UPDATE [SomeTable] 
SET Check = 'No Match' 

UPDATE S 
SET Check = 'Match' 
FROM 
[SomeTable] S JOIN dbo.Customers C ON s.CustomerID = C.CustomerId 
AND C.cus_title IN 
('MRS','DAME','SISTER','MISS','MADAME','LADY','MS') and C.cus_gender <> 
'F' 
JOIN Pv_Customer P ON S.CustomerID = P.CustomerID AND 
P.cus_title IN ('FATHER','SIR','MR') 
and P.cus_gender <> 'M' 

Answer 3

这听起来像一个乔b为计算列！与运行UPDATE不同，计算列具有在添加新行或更新现有行时自动更新的优点。

创建这样的函数：

CREATE FUNCTION [dbo].[fn_GenderMatch](@title VARCHAR(50), @gender CHAR(1)) RETURNS VARCHAR(10) 
WITH SCHEMABINDING AS 
BEGIN 
    IF (((@title IN ('MRS', 'MS', 'MISS')) AND (@gender = 'M')) 
     OR 
     ((@title IN ('MR', 'SIR', 'FATHER')) AND (@gender = 'F'))) 
    BEGIN 
     RETURN 'non-match' 
    END 
    RETURN 'match' 
END 
GO 

的WITH SCHEMABINDING，如果你想在此列的索引是很重要的！虽然，如果你想索引，我建议改变函数返回BIT。哎呀，使用BIT也更适合存储，也适合其他比较。

然后改变你的表是这样的：

ALTER TABLE [dbo].[customer] ADD [MisMatch] AS [dbo].[fn_GenderMatch]([cus_title],[cus_gender])