July 26, 2002 at 12:12 am
hi
for the update multiple table codes below... it is to update the whole db for all those customers(every country) who own the productOwn_ID = 1, but how to add one more sentence to update only taiwan customers as their country_ID = 8?
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)
pls giv me some advice. Thank You! =)
July 26, 2002 at 1:54 am
You do not say where the country_ID is stored, is it in a customer table? If so join ProductOwn_Details to customer and filter on country_ID = 8.
e.g.
insert into Newsletter_Details
(
customer_ID, NewsletterSubscribe_ID
)
select customer_ID, 2
from ProductOwn_Details pod
inner join Cust c on pod.customer_ID = c.customer_ID
where ProductOwn_ID = 1
and c.country_ID = 8
and not exists --Do not want duplicates
(select customer_ID
from Newsletter_Details
where customer_ID = pod.customer_ID
and NewsletterSubscribe_ID = 2)
Regards,
Andy Jones
.
July 28, 2002 at 7:48 pm
hi i try to run the sql but it occured some error:
insert into Newsletter_Details
(
customer_ID, NewsletterSubscribe_ID
)
select customer_ID, 2
from ProductOwn_Details pod
inner join Cust c on pod.customer_ID = c.customer_ID
where ProductOwn_ID = 1
and c.country_ID = 8
and not exists --Do not want duplicates
(select customer_ID
from Newsletter_Details
where customer_ID = pod.customer_ID
and NewsletterSubscribe_ID = 2)
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Customer_ID'.
pls help... thanx.. =)
July 29, 2002 at 4:38 am
You'll have to qualify it in the select with the table name. It's ambiguous because it exists in both tables.
Andy
July 29, 2002 at 7:41 pm
hi, which customer_ID do i need to put prefix of the table name to avoid error to occur???? p/s: i try to put prefix at the customer_ID but got errors.
insert into Newsletter_Details
(
customer_ID, NewsletterSubscribe_ID
)
select customer_ID, 2
from ProductOwn_Details pod
inner join Cust c on pod.customer_ID = c.customer_ID
where ProductOwn_ID = 1
and c.country_ID = 8
and not exists --Do not want duplicates
(select customer_ID
from Newsletter_Details
where customer_ID = pod.customer_ID
and NewsletterSubscribe_ID = 2)
pls giv some advice ... thank alot...!!!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply