通过加入 3 个表来更新 SQL [英] Update SQL by joining 3 tables

问题描述

使用下面的表结构，我需要将行 ID 插入到批准的表中，使用位置名称作为查找值.我该怎么做?

Using the below table structure i need to insert the Line ID into the approved table, using the location name as the lookup value. How do i go about doing this?

我开始使用下面的代码，但它没有做太多.在 SQL 连接方面不是很好，因此非常感谢任何帮助.

I started off using the below code, but it doesn't do much. Not that great on SQL joins so any help is much appreciated.

UPDATE dbo.Approved 
SET dbo.Approved.Groupid=dbo.Lines.ID 
FROM dbo.Lines,dbo.Approved, dbo.Locations 
WHERE dbo.Approved.Location = dbo.Locations.Location_Name

已批准

ID (PK) | Incident             | Location     | GroupID
--------------------------------------------------------
1       | Theft of luggage     |Oxford Circus | Null
2       | Theft of bag         |Kings Cross   | Null

线

ID (PK) | Line_Name      | 
--------------------------
1       | Central        |
2       | Northern       |
3       | Circle         |

地点

ID (PK) | Location_Name  | LineID
---------------------------------
1       | Oxford Circus  |1
2       | Kings Cross    |2
3       | Victoria       |3

推荐答案

这是我的建议:

UPDATE t1.Approved
SET t1.Groupid = t2.ID
FROM dbo.Approved t1
INNER JOIN dbo.Locations t2 ON t1.Location = t2.Location_Name

您不需要 Lines 表，因为您想要插入的只是 LineID 而不是 Line_Name.

You don't need the Lines table because what you want to insert, at the end is just the LineID not the Line_Name.

希望能帮到你，

这篇关于通过加入 3 个表来更新 SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！