填充联结表Transact-SQL中的列

I have two tables with a many-to-many relationship. The relevant part of the tables look like this:

我有两张表与多对多的关系。表格的相关部分如下所示:

Filmlager [ProgramID, Type]

Genre [GenreID, GenreTitle]

Since there's a many-to-many relationship I've created a junction table looking like this:

由于存在多对多关系,我创建了一个如下所示的联结表:

ProgramGenre [ProgramID, GenreID, GenreOrder]

Now I want to set the value 1 in the GenreOrder column where Filmlager.Type=Genre.GenreTitle

现在我想在GenreOrder列中设置值1,其中Filmlager.Type = Genre.GenreTitle

So far I've managed to get the ProgramID and GenreID where I want to update the GenreOrder by using the following query:

到目前为止,我已经设法通过使用以下查询获取我想要更新GenreOrder的ProgramID和GenreID:

 SELECT p.ProgramID, p.GenreID
 FROM ProgramGenre p, Filmlager f, Genre g
 WHERE p.ProgramID = f.ProgramID
 AND p.GenreID = g.GenreID
 AND f."Type"= g.GenreTitle;

I'm not sure how to proceed from here. The part I can't figure out is how to compare the combination of two columns (the result from the query above) with ProgramID and GenreID in the ProgramGenre table. I've tried using nested queries without success.

我不知道怎么从这里开始。我无法弄清楚的部分是如何比较两个列的组合(上面的查询的结果)与ProgramGenre表中的ProgramID和GenreID。我尝试过使用嵌套查询但没有成功。

I'm using Transact-SQL in Microsoft SQL Server Management Studio

我在Microsoft SQL Server Management Studio中使用Transact-SQL

Thanks in advance!

提前致谢!

1 个解决方案

#1

Just do an update:

只是做一个更新:

UPDATE p SET GenreOrder = 1
FROM ProgramGenre p
JOIN Filmlager f ON p.ProgramID = f.ProgramID
JOIN Genre g ON p.GenreID = g.GenreID
WHERE f."Type"= g.GenreTitle;