Select Update

Question

Select Update

sc-w

Hall of Fame

Points: 3667
More actions
August 9, 2010 at 5:44 am

#142340

Hi,
I have a field "mt_postcode" in a sub entity table called "sales" that i want to update with the value of the same named field "mt_postcode" in the entity table "contact".
The problem i have is this a one to many relationship so there will be many rows in "sales" per single row in the "contact" table but there is also a link table called "link_to" that links the above two tables.
This is the join that links these tables.
SELECT c.mt_postcode
FROM CONTACT AS C
INNER JOIN linkto AS l ON C.UNIQUEID = l.lentityid left join sales as s on l.luniqueid = s.uniqueid
I was thinking something like this but i want to be sure first before i hit the button 🙂 Could someone run there eyes over the and give me some input? Thanks for looking.
update wce_sales set mt_postcode = c.mt_postcode
where EXISTS (
SELECT c.mt_postcode
FROM CONTACT AS C
INNER JOIN linkto AS l ON C.UNIQUEID = l.lentityid left join sales as s on l.luniqueid = s.uniqueid
)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i think the UPDATE FROM syntax would help in this case; i took a wild guess and hoped there was a relationship between a column named entityID?!?! and your two tables contact and :wce_sales ;

if my guess does not help give us more details on the relationships bewtween the tables.

UPDATE .

SET m.mt_postcode = c.mt_postcode

--SO easy to test!

--SELECT m.entityI,m.mt_postcode,c.mt_postcode

FROM wce_sales m

INNER JOIN CONTACT c on m.entityID = c.entityid

INNER JOIN linkto AS l

ON C.UNIQUEID = l.lentityid

LEFT JOIN sales as s

ON l.luniqueid = s.uniqueid

WHERE mt_postcode <> c.mt_postcode

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sc-w Hall of Fame Points: 3667 More actions · Answer 2

Thanks for the reply, i will work through it and see if i can re-work this. Unfortunatly there is no direct link between the contact and sales table. To link a contact with there sales i would use this select query.

Select mt_postcode from contact as c inner join link_to as l on c.uniqueid = l.lentityid left join sales as s on l.luniqueid = s.uniqueid

So the the entity and sub entity table have a field called uniqueid and the only way to link them is via the link_to

Hope that helps.