CODEWITHSUNDEEP
sqlsql-serversql-server-2012
David
David

Reputation: 593

SQL Server Updating a column in one table with column from another

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.

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.

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

enter image description here

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

enter image description here

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?

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.

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.

Upvotes: 0

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use join in the update statement.

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.

Upvotes: 1

Related Questions