数据库sql查询以进行匹配 [英] database sql query for matching

问题描述

请帮助我编写关于follcensn的查询:

我在sql中有两个表，一个是"TABLE1"，它包含一个字段"NAMES"，其中包含大约500个人的名称列表.

另一个表是"TABLE2"，一个字段是"NAMES OF STUDENTS"，另一个字段是"GENDER".

我想将"Table2"的名称与"TABLE1"进行匹配，如果找到匹配项，则查询将在性别列中添加"male".

Please help me to write a query of foll scenarion:

i have tow tables in sql, one is "TABLE1" it contains a field "NAMES" thats contains list of names of about 500 persons.

other table is of "TABLE2", one field is "NAMES OF STUDENTS" and other is "GENDER" .

i want to match the names of "Table2" with "TABLE1" and if match is found, than the query will add "male" in gender column.

推荐答案

如果我没看错，您的TABLE1是一个男性列表，而TABLE2是一个两个性别的学生列表，因此如果表1包含一个学生的姓名，那么那个学生是一个男性，您想更新该列吗? br/>

If I read you correctly, your TABLE1 is a list of males, while TABLE2 is a list of students of both genders, so if table 1 contains the name of a student, then that student is a male and you want to update the column?

Update table2
Set Gender = ''male''
where exists
(
select 1 from table1 where table1.name = table2.name
)

尝试一下

select *, ''Male'' as [Gender] from Table1 inner join Table2 on Table1.Name = Table2.Name

或

OR

select *, case when Table_2.Name is null then NULL else ''Male'' end from Table_1 left join Table_2 on Table_1.Name = Table_2.Name

尝试一下...

Try this...

 If Exists(select T1.Name from table1 T1 left join table2 T2
       on T1.name=T2.Name)
 begin
 DECLARE @Name varchar(100);
 select @Name= T1.Name from table1 T1 left join table2 T2
    on T1.name=T2.Name
 update table2 set gender=''male'' where Name=@Name;
end

这篇关于数据库sql查询以进行匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！