March 23, 2010 at 6:42 am
Hi All,
I am having problems trying to update a postcode from one table to another depending no a relationship set in a third table.
I have t1 with a list of contacts, IDs, address, postcodes etc (needs updating).
t2 contains students with IDs and postcodes I want to update from.
t3 contains the student ID's and contacts IDs plus details of the link (as contacts are linked to many students). It also states if the contact is linked to the address of the student (hlink).
I want to update t1.PostCode from t2.Postcode where they are in t3 and linked by address.
I done a little Google and came up with this statement but it keeps returning more then one value, so terminates 🙁
Update t1 set HomePostCode =
(select HomePostCode from t2
where date = '2010' and studentid in
(select studentid from t3 where date = '2010' and hlink = 'Y' and contactid in
(select contactid from t1 where date = '2010')))
where date = '2010'
Any help will be much appreciated,
Scott
March 23, 2010 at 7:01 am
I think I have worked it out trying something else, need to test it fully.
UPDATE t1
SET HomePostCode = t2.HomePostCode
FROM t2 INNER JOIN
t1 ON t2.Date = t1.Date INNER JOIN
t3 ON t1.Date = t3.Date AND t2.Date = t3.Date AND
t1.ContactId = t3.ContactId AND t2.StudentId = t3.StudentId AND
t3.HLink = 'Y'
Scott
March 23, 2010 at 7:02 am
Scott,
(Edit: ohh just saw that you came back with a reply)
My suggestion is not to use this query below. Its just to show you that you need to write it along these lines.
Update t1
set t1.HomePostCode = t2.homePostCode -- Select *
FROM TABLE1 T1
JOIN TABLE2 T2
ON T1.ID = T2.ID
WHERE T1.date = '2010'
AND T2.Date = '2010'
AND EXISTS (
SELECT 1
FROM TABLE3 T3
Where T3.ID = T2.ID
AND T3.Contact = T2.Contact
AND T3.date = '2010'
AND hlink = 'Y')
Please go through this article, come back and post your query like that, you would get far better responses. Thanks.
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
---------------------------------------------------------------------------------
March 23, 2010 at 7:33 am
Hi,
UPDATE t1
SET HomePostCode = t2.HomePostCode
FROM t2 INNER JOIN
t1 ON t2.Date = t1.Date INNER JOIN
t3 ON t1.Date = t3.Date AND t2.Date = t3.Date AND
t1.ContactId = t3.ContactId AND t2.StudentId = t3.StudentId AND
t3.HLink = 'Y'
Think that's better 😀 thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply