noob SQL question here. I have a database in SQL Server 2012 and access to another MySQL database with information I'd like to pull.
noob SQL问题在这里。我在SQL Server 2012中有一个数据库，并且可以使用我想要提取的信息访问另一个MySQL数据库。
I would like to update a column representing quantities of a workstation, and that information is present on the other database. I would like to structure a query that updates quantities of all items where model numbers match in each database.
我想更新表示工作站数量的列，该信息存在于其他数据库中。我想构建一个查询来更新每个数据库中型号匹配的所有项目的数量。
I'll include two select statements to demonstrate what I'd like to work with.
我将包括两个select语句来演示我想要使用的内容。
The data from the other database I want to pull from.
我要从其他数据库中提取的数据。
SELECT *
FROM OPENQUERY(S3MONITOR,
'SELECT count(BuildDriver) AS ''# of Workstations'', BuildDriver AS ''Workstation Model''
FROM workstation
GROUP BY BuildDriver')
Which produces this result
这产生了这个结果
My database that I'd like to update.
我想要更新的数据库。
SELECT NumOfDevices, Model
FROM dbo.Currency
INNER JOIN dbo.SubCategory
ON Currency.SubCategoryId = SubCategory.SubCategoryId
WHERE SubCategory.SubCategoryName = 'WorkStation';
Which produces this result
这产生了这个结果
The models will be updated in my database to make sure model names correspond to one another but in the interim I'd like to test a query using the M93z. So what would be the most ideal way of updating NumofDevices in my database using # of Workstations in the other database, where the model names are equal?
模型将在我的数据库中更新，以确保模型名称彼此对应，但在此期间，我想使用M93z测试查询。那么，使用另一个数据库中的#of Workstations来更新数据库中NumofDevices的最理想方式是什么？
Sorry if this is an obvious question, I'm still an SQL noob and it's still not intuitive to me when it comes to these kinds of things.
对不起，如果这是一个显而易见的问题，我仍然是一个SQL菜鸟，当涉及到这些事情时，它仍然不直观。
EDIT: The end goal is to routinely update all Workstation quantities nightly through an SQL Server Agent scheduled job but I'll cross that bridge when I come to it (I actually have some faith that I'll be able to apply the query solution to this job as soon as it's figured out) but I want to include this information in the event that it changes any of your suggestions.
编辑：最终目标是通过SQL Server代理预定作业每晚定期更新所有工作站数量，但是当我来到它时我会越过那个桥（我实际上有一些信心，我将能够应用查询解决方案这项工作一出现就知道了）但是我希望在它改变你的任何建议的情况下包含这些信息。
1 个解决方案
#1
1
You can use join in the update statement.
您可以在update语句中使用join。
with newvals as (
SELECT *
FROM OPENQUERY(S3MONITOR,
'SELECT count(BuildDriver) AS ''# of Workstations'', BuildDriver AS ''Workstation Model''
FROM workstation
GROUP BY BuildDriver'
) oq
)
update c
set NumOfDevices = newvals.num_workstations
from dbo.Currency c join
dbo.SubCategory sc
on c.SubCategoryId = sc.SubCategoryId join
newvals
on newvals.model = c.model
where sc.SubCategoryName = 'WorkStation';
Note: This updates values already in the table. You might want to use merge if you want to add new rows as well. If this is the case, ask another question, because this question is specifically about updating.
注意：这会更新表中已有的值。如果要添加新行，也可以使用合并。如果是这种情况，请提出另一个问题，因为这个问题具体是关于更新。
#1
1
You can use join in the update statement.
您可以在update语句中使用join。
with newvals as (
SELECT *
FROM OPENQUERY(S3MONITOR,
'SELECT count(BuildDriver) AS ''# of Workstations'', BuildDriver AS ''Workstation Model''
FROM workstation
GROUP BY BuildDriver'
) oq
)
update c
set NumOfDevices = newvals.num_workstations
from dbo.Currency c join
dbo.SubCategory sc
on c.SubCategoryId = sc.SubCategoryId join
newvals
on newvals.model = c.model
where sc.SubCategoryName = 'WorkStation';
Note: This updates values already in the table. You might want to use merge if you want to add new rows as well. If this is the case, ask another question, because this question is specifically about updating.
注意：这会更新表中已有的值。如果要添加新行，也可以使用合并。如果是这种情况，请提出另一个问题，因为这个问题具体是关于更新。