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

Question

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!

I'm not really following the Job column. So, the ID from People should be the same as the ID in companies?, and then you want to update that very same ID to the Job column? — Lamak
– Lamak, Commented Sep 26, 2014 at 20:08
The Job column should contain the ID of the company where the person works. I'll update the question to be more clear. — Benjin
– Benjin, Commented Sep 26, 2014 at 20:30
and how are we supposed to know the company where a person works? — Lamak
– Lamak, Commented Sep 26, 2014 at 20:32
I'm a nearly-absolute-beginner at SQL Server. For the purposes of the question, it's what I (the query-writer) know, but want to change the database to reflect that. — Benjin
– Benjin, Commented Sep 26, 2014 at 20:35

Fernando Martín Besteiro · Accepted Answer · 2014-09-26 20:33:55Z

1

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.

answered Sep 26, 2014 at 20:33

Fernando Martín Besteiro

7635 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Benjin Over a year ago

This looks to be exactly what I wanted. Not knowing that I could embed a SELECT inside another query was the missing piece. Thanks!

Fernando Martín Besteiro Over a year ago

If it's the correct answer, do not forget to add it as a correct answer. Thanks

Arun · Accepted Answer · 2014-09-26 20:12:03Z

0

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;

answered Sep 26, 2014 at 20:12

Arun

9616 silver badges14 bronze badges

6 Comments

crthompson Over a year ago

This is the exact answer I posted, but I deleted it because as @Lamak points out in the comments, it appears that OP wants to join the table on JOB.. not ID

Lamak Over a year ago

Even if the syntax is correct, the logic behind it doesn't make sense. In this scenario, the JOIN is absolutely not necessary, since it could simply update the value of the People ID

Arun Over a year ago

@paqogomez Yeah, I see what he says now. However that doesn't make any sense as CompanyName doesn't exist in the people column.

Lamak Over a year ago

@Arun Exactly, the requirements, as currently written, don't really make much sense

Arun Over a year ago

@Benjin You can add a subquery inside the WHERE clause. so WHERE (SELECT whatever from whatever where whatever=blah) technet.microsoft.com/en-us/library/ms189575(v=sql.105).aspx

|

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related