SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Simple SQL question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Binayoke
Starting Member

Australia
1 Posts

Posted - 12/10/2012 :  19:22:44  Show Profile  Reply with Quote
Hi,

I have been working my way through the exercises at 'Learn SQL The Hard Way' and im currently stuck on this particular question http://sql.learncodethehardway.org/book/learn-sql-the-hard-waych14.html#x19-5600014.

To keep it simple:

I have a table called PET containing 'id' and 'parent' fields, and a table called PERSON (this table is irrelevant to the question im asking).
Then there is a relation called PERSON_PET containing 'person_id' and 'pet_id' fields.

What I need to know is how to find a pet's parent(owner) by matching the PET.id with the PERSON_PET.pet_id and selecting that record's PERSON_PET.person_id.

Here are the tables:

CREATE TABLE PERSON (
id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
age INTEGER,
dead TEXT,
phone_number TEXT,
salary FLOAT,
dob DATETIME
);

CREATE TABLE PET (
id INTEGER PRIMARY KEY,
name TEXT,
breed TEXT,
age INTEGER,
dead TEXT,
dob DATETIME,
parent INTEGER
);

CREATE TABLE PERSON_PET (
person_id INTEGER,
pet_id INTEGER,
purchased_on DATETIME
);

To select the person_id I have the following statement:

SELECT PERSON_PET.person_id
FROM PERSON_PET, PET
WHERE PERSON_PET.pet_id = PET.id


However, how do I make an update query to put this person_id into the parent field? Following is the code I think I need to use, however its incomplete.


UPDATE pet SET parent = (SELECT PERSON_PET.person_id
FROM PERSON_PET, PET
WHERE PERSON_PET.pet_id = PET.id)
WHERE ?????
;

Am I on the right track?

Any help would be appreciated.

Regards,

Peter



Edited by - Binayoke on 12/10/2012 19:40:29

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 12/10/2012 :  23:19:00  Show Profile  Reply with Quote
May be this?

UPDATE p
SET p.parent = pp.person_id
FROM PET p
JOIN PERSON_PET pp ON pp.pet_id = p.id

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000