August 15, 2008 at 1:42 pm
I have 3 tables
People - id, name, mail_id
People_assingment - id, org_code
People_request - Key_id, org_code, mail_id
I need to write a script that will sync all the three tables right.
Example:
People
--------
id, Name, email
1, John, John@state.com
People_assignment
------------------
id, org_code
1, 5000
1, 6000
1, 8000
People_request
---------------
key_id, org_code, email
19 , 2000 John@state.vom
20, 5000, John@state.com
21, 6000, John@state.com
I need to join the tables and compare People_assignment and People_request table.
Delete unwanted records that is there in people_request table that are not matched in people_assignment.
Insert into people_request any missing records that are present in people_assignment.
In the above example,
Delete the unwanted record '2000' for john that is not there in people_assignment table
Insert into people_request a record for org_code '8000' for John. It has only 2. It must be nmatched with the people_assignment table.
August 15, 2008 at 2:00 pm
insert into people_request(org_code, email)
select pa.org_code, p.email
from people p
inner join people_assignment pa
on p.id= pa.id
left outer join people_request pr
on p.email = pr.email
and pa.org_code = pr.org_code
where pr.key_id is null
That should give you your insert. Try it, see if it does what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 15, 2008 at 2:32 pm
Thanks, i will try that.
Also can i implement this snippet inside a cursor? coz a person will have multiple entries in Assignment table and all the records need to be updated/inserted iin people_request also.
So that it will fetch one record from assignment, check if it present in request.
If not present, it will insert.
August 18, 2008 at 8:25 am
If you run that code as-is, you shouldn't need the cursor.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 18, 2008 at 10:22 am
Basically - cursors are evil.
Never, ever, use one unless it's absolutely 100% unavoidable. It is in the vast majority of cases, one way or another.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply