September 27, 2007 at 8:17 pm
table in which update has to be done --------------------Table a
table from where update has to be made----------------Table b
table structure of Table a--------------------contactid,lastname,firstname
table structure of table b-------------------leacode,lastname,firstname
table structure of table c----------------------------contactid,leacode
contactid is an identity column
how will i write my update/insert
September 27, 2007 at 8:22 pm
Best (9/27/2007)
table in which update has to be done --------------------Table atable from where update has to be made----------------Table b
table structure of Table a--------------------contactid,lastname,firstname
table structure of table b-------------------leacode,lastname,firstname
table structure of table c----------------------------contactid,leacode
contactid is an identity column
how will i write my update/insert
tough one, because it's not a clean join; table B should have the contactid isntead of f/lnames, because names are not unique.
insert into tableC
select contactid,leacode
from tablea
inner join tableb on tablea.lastname=tableb.lastname AND tablea.firstname=tableb.firstname.
Lowell
September 27, 2007 at 8:29 pm
can i write my query for insert and update like this do you think this approach is ok or not
Insert into Table a
Select b.firstname,b,lastname
Table a right outer join Table b
on a.lastname = b.lastname
and a.firstname = b.firstname
where a.lastname is null
Insert into Table c
Select a.contactid ,b.leacode
from Table a left outer join Table c
on a.contactid = c.contactid
right outer join Table b
on a.lastname = b.lastname
and a.firstname = b.firstname
where c.contactid is null
--------------------------------------------------------------
Update a
set a.address = b.address,
a.whateveryouwant = b.whateeveryouwant
from Table a left outer join Table c
on a.contactid = c.contactid
right outer join Table b
on a.lastname = b.lastname
and a.firstname = b.firstname
where c.contactid is not null
and a.contactid is not null
September 27, 2007 at 8:47 pm
1. Update must go first. There is no need to update rows you just inserted.
2. Update must contain only INNER joins. If data is missing there is nothing to update. Missing data will be populated with following inserts.
3. For inserts use WHERE NOT EXISTS instead of outer joins.
INSERT INTO TableA
SELECT ...
FROM ...
WHERE NOT EXISTS (select 1 from TableA where ...)
Otherwise fine.
_____________
Code for TallyGenerator
September 27, 2007 at 9:15 pm
i am not sure in this insert after should the select be from table a as i am doing or it should be table b
Insert into Table a
Select
b.firstname,
b,lastname
from Table a right outer join Table b
on a.lastname = b.lastname
and a.firstname = b.firstname
where a.lastname is null
Insert into Table c
Select
a.contactid ,
b.leacode
from Table a left outer join Table c
on a.contactid = c.contactid
right outer join Table b
on a.lastname = b.lastname
and a.firstname = b.firstname
where c.contactid is null
September 27, 2007 at 9:44 pm
Insert into TableA
Select b.firstname, b,lastname
FROM TableB b
WHERE NOT EXISTS
(select 1 from TableA a
where a.lastname = b.lastname and a.firstname = b.firstname
)
Same approach for TableC.
_____________
Code for TallyGenerator
September 27, 2007 at 9:57 pm
can you tell me wht do you exactly mean same approach for table c
September 27, 2007 at 10:00 pm
FROM TableA a
INNER JOIN TableB b ON ...
WHERE NOT EXISTS (select 1 from TableC c ...)
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply