Simple SQL question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Simple SQL question

Author

Topic

Binayoke
Starting Member

1 Post

Posted - 2012-12-10 : 19:22:44

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-10 : 23:19:00

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources