|
|
Posted by Hugo Kornelis on 02/16/06 22:03
On 16 Feb 2006 10:39:45 -0800, ibiza wrote:
>Hi SQL fans,
(snip)
>My problem is : When I update a portfolio, I must update all the
>associated titles in it. That means that titles can be either removed
>from the portfolio (a folio does not support the title anymore), added
>to it (a new title is supported by the folio) or simply updated (a
>title stays in the portfolio, but has its weight changed)
>
>For example, if the portfolio #2 would contain :
>
>[ PortfolioTitle ]
>id | idFolio | idTitre | poids
>1 2 1 10
>2 2 2 20
>3 2 3 30
>
>and I must update the PortfolioTitle based on these values :
>
>idFolio | idTitre | poids
>2 2 20
>2 3 35
>2 4 40
>
>then I should
>1 ) remove the title #1 from the folio by deleting its entry in the
>PortfolioTitle table
>2 ) update the title #2 (weight from 30 to 35)
>3 ) add the title #4 to the folio
Hi ibiza,
Deleting all rows, then re-inserting (as you do now) is one of the two
popular ways to acheive this. The second is (assuming that the new
values are stored in the table NewFolioTitles):
-- Step 1: DELETE rows that are no longer needed
DELETE FROM FolioTitles
WHERE NOT EXISTS
(SELECT *
FROM NewFolioTitles AS n
WHERE n.idFolio = FolioTitles.idFolio
AND n.idTitre = FolioTitles.idTitre)
-- Step 2: UPDATE rows that have been changed
UPDATE FolioTitles
SET poids =
(SELECT poids
FROM NewFolioTitles AS n
WHERE n.idFolio = FolioTitles.idFolio
AND n.idTitre = FolioTitles.idTitre)
WHERE EXISTS
(SELECT *
FROM NewFolioTitles AS n
WHERE n.idFolio = FolioTitles.idFolio
AND n.idTitre = FolioTitles.idTitre
AND n.poids <> FolioTitles.poids)
-- Or, as an alternative, use the version below;
-- this is shorter and often faster, but uses
-- proprietary code and is therefor less portable
-- UPDATE f
-- SET poids = n.poids
-- FROM FolioTitles AS f
-- INNER JOIN NewFolioTitles AS n
-- ON n.idFolio = f.idFolio
-- AND n.idTitre = f.idTitre
-- WHERE n.poids <> f.poids
-- Step 3: INSERT new rows
INSERT INTO FolioTitles (idFolio, idTitre, poids)
SELECT n.idFolio, n.idTitre, i.poids
FROM NewFolioTitles AS n
LEFT JOIN FolioTitles AS f
ON f.idFolio = n.idFolio
AND f.idTitre = n.idTitre
WHERE f.idFolio IS NULL
This is just the basic outline - you should enclose it in a transaction
and add proper error handling.
--
Hugo Kornelis, SQL Server MVP
[Back to original message]
|