CODEWITHSUNDEEP
sqlsql-server
Benjin
Benjin

Reputation: 2409

Pulling data from one table to use in another query for SQL Server?

I'm in the process of learning SQL Server, and so I'm not quite sure what the proper terms to search for something like this are.

I have two tables, People (ID, FirstName, LastName, Job):

ID   FirstName LastName   Job
 1     Barack    Obama    Null  // Obama is unemployed

and Companies (ID, CompanyName, City, State):

ID   CompanyName           City        State
 1   Legislative Branch    Washington   DC
 2   Executive Branch      Washington   DC
 3   Judicial Branch       Washington   DC

I know I can run something like 

update Persons 
set Job = '2' 
where ID = 1;

to do that manually, but that requires me to know that Executive Branch's ID is 2.

How would I write a query that looks up CompanyName == 'Executive Branch' in the Companies table then uses the ID from there?

ID   FirstName LastName   Job
----------------------------------
 1     Barack    Obama     2  //Obama now works for the Executive Branch

Possibly related, is it considered "bad form" to use ID for both of those tables, or should one be PersonID and the other CompanyID?

Thanks!

Upvotes: 0

Views: 1869

Answers (2)

Fernando Martín Besteiro
Fernando Martín Besteiro

Reputation: 753

How would I write a query that looks up CompanyName == 'US Gov' in the Companies table then uses the ID from there?

As simple as:

SELECT ID
FROM Companies
WHERE CompanyName = 'US Gov'

Added into your example:

UPDATE People
SET Job = (SELECT ID
           FROM Companies
           WHERE CompanyName = 'US Gov')
WHERE ID IN (id1, id2, id3, ..., idn)

being id1, id2, etc the IDs of the people you want to modify.

Upvotes: 1

Arun
Arun

Reputation: 951

From what I understand on your post your trying to update the job field to the corresponding ID field on the other table. If so, you would do something like this:

UPDATE People p
SET
   p.job = c.id
FROM
   p
INNER JOIN
   Companies c
ON p.id = c.id
WHERE
   c.id = 1;

Upvotes: 0

Related Questions