September 22, 2008 at 6:38 am
Hi Gurus,
I have a table Customer as follows
CustID CustName
1 ABC
2 XYZ
3 PQR
Another table CustPriceList
CustID PrdCode Price
1 ItemA 10
1 ItemB 20
A third table ItemPL
PrdCode Price
ItemA 10
ItemB 20
I want a script to insert the price list for those customers not in CustPriceList
I did this not manually for the 15 customers that are there.But I want for future.
September 22, 2008 at 7:28 am
Hello,
Before I give you the script I should point out that it seems to me that your table CustPriceList is redundant. It is just a product of the tables Customer and ItemPL i.e. all rows in Customer combined with all rows in ItemPL.
Anyway, the following should work:
INSERT INTO
CustPriceList
SELECT
AC.CustID
AC.PrdCode,
AC.Price
FROM
(
SELECT
C.CustID
I.PrdCode,
I.Price
FROM
ItemPL AS I
CROSS JOIN
Customer AS C
)
AS AC
LEFT OUTER JOIN
CustPriceList AS CPL
ON
AC.CustID = CPL.CustID
ANDAC.PrdCode = CPL.PrdCode
ANDAC.Price = CPL.Price
WHERE
CPL.CustID IS NULL
ANDCPL.PrdCode IS NULL
ANDCPL.Price IS NULL
Hope this helps,
Ash
September 22, 2008 at 7:58 am
This is more of a shot gun approach.
insert CustPriceList
(CustID, Prdcode, Price)
select c.CustId,
i.PrdCode,
i.Price
from Customer c,
itemPL i
where c.CustID not in ( select CustId from CustPriceList )
I agree the CustPriceList is redundant unless you are just building the customers price list from a default list of products from ItemPL and then are going to customize prices by each client in CustPriceList later.
Regards,
Terry
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply