July 16, 2002 at 1:53 am
I require to peform update operation on more than one table based on the values being updated in a table.
-customer_info(customer_ID, given_name, address, phone)
-Newsletter_Details(customer_ID, NewsletterSubscribe_ID)
-ProductOwn_Details(customer_ID, ProductOwn_ID)
-NewsletterSubsribe(NewsletterSubscribe_ID, Newsletter_Name, Newsletter_Desc)
-ProductOwn(ProductOwn_ID, ProductName, ProductDesc)
i need to update the table by:-
if the customer own ProductOwn_ID = 1 then the newsletterSubscribe_ID = 2
so which table should i update and how???
pls giv me some advice !!! thanx
July 16, 2002 at 2:29 am
Need some more information here: -
Should this update be performed as a one off job or each time the customer's products are updated?
Also, you don't give any information on the table structure so I'm guessing:-
ProductOwn_Details is a link table between customers and products they have purchased.
Newsletter_Details is a link table between customers and newsletters they should receive.
If a customer purchases a particular product (ProductOwn_ID = 1) they should receive a particular newsletter (NewsletterSubscribe_ID = 2).
So a one off job would be somthing like: -
insert into Newsletter_Details
(
customer_ID
, NewsletterSubscribe_ID
)
select
customer_ID
, 2
from
ProductOwn_Details pod
where
ProductOwn_ID = 1
and not exists --Do not want duplicates
(
select
customer_ID
from
Newsletter_Details
where
customer_ID = pod.customer_ID
and NewsletterSubscribe_ID = 2
)
This insert could also be put into a trigger on ProductOwn_Details to fire each time a record was inserted (i.e. a customer made a purchase).
create trigger tr_ProductOwn_Details on ProductOwn_Details for insert as
insert into Newsletter_Details
(
customer_ID
, NewsletterSubscribe_ID
)
select
customer_ID
, 2
from
inserted ins
where
ProductOwn_ID = 1
and not exists --Do not want duplicates
(
select
customer_ID
from
Newsletter_Details
where
customer_ID = ins.customer_ID
and NewsletterSubscribe_ID = 2
)
Regards,
Andy Jones
.
July 16, 2002 at 11:41 pm
hi i m using one off job to update the database but if i want to minimise the number of customer by smaller the group how to add in one more sentence to tell them tat onli taiwan customer need to update the productOwned and newsletter??
for the country_ID it is found at the table(customer_info)???
thanx
regards,
heero =)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply