Updating a table with modified records from another table
I am trying to insert values from a table to another table. The thing is I want it such that only a row in the source table that does not exist in the destination table should be inserted. I have set each table to have a column that automatically increments so if there are 6 rows in table 1 and only 5 rows in table 2 , only row 6 in table 1 should be inserted into table 2. Here is my code
`INSERT INTO Portfolio
([SYMBOL], [SECURITY],[DATE],[OPENING PRICE],[CLOSING PRICE],[UNITS],[BROKER], [BROKERAGE COMMISSION],
[SECURITY LEVY],[CONTRACT STAMP],[VAT],[NET VALUE],[PER SHARE VALUE],[PURCHASE PRICE],[GAIN / LOSS],[GAIN/LOSS %])
Company.[SYMBOL], Company.[SECURITY],BuyPortfolio.[DATE],Pricelist.[OPENING PRICE],Pricelist.[CLOSING PRICE],BuyPortfolio.[UNITS],BuyPortfolio.[BROKER],BuyPortfolio.[BROKERAGE COMMISSION],
BuyPortfolio.[SECURITY LEVY],BuyPortfolio.[CONTRACT STAMP],BuyPortfolio.[VAT],BuyPortfolio.[NET VALUE],
BuyPortfolio.[PER SHARE VALUE], BuyPortfolio.[PURCHASE PRICE],
((Pricelist.[CLOSING PRICE]* BuyPortfolio.[PER SHARE VALUE])-BuyPortfolio.[PURCHASE PRICE]) AS "GAIN / LOSS",
(((Pricelist.[CLOSING PRICE]* BuyPortfolio.[PER SHARE VALUE])-BuyPortfolio.[PURCHASE PRICE])/BuyPortfolio.[PURCHASE PRICE]) * 100 AS "GAIN/LOSS %"
FROM Company JOIN Pricelist
on Company.symbol = Pricelist.symbol JOIN BuyPortfolio
on Pricelist.symbol = BuyPortfolio.symbol
WHERE BuyPortfolio.[STOCK ID] NOT LIKE Portfolio.[STOCK ID]'
The Portfolio is the destination table and the BuyPortfolio is the source table. What i cannot get is how to structure the WHERE clause.So if anyone can help me get it right or an alternate procedure on how to do it, I would be very grateful
1 Year Ago
Related Article:How to Join a table which has no fk with 2nd table?
is a solved Databases discussion thread by khushhappy that has 1 reply, was last updated 5 months ago and has been tagged with the keywords: how, to, join, a, table, which, has, no, fk, with, 2nd, table?.
You can't assume that the autoincrementing ID's will match up, so I'm pretty sure @Hearth's solution would not get the desired results.
It might be useful to know more about your source table. Is there a unique natural key to use? If so, you could LEFT JOIN to Portfolio on BuyPortfolio.[SYMBOL] = Portfolio.[SYMBOL] (and whatever else is in the natural key), and use this WHERE clause;
WHERE Portfolio.[SYMBOL] IS NULL
It would probably perform better than using "NOT IN", too.