How to correctly update a table which values can be either inserted/updated/deleted on update?

Hi SQL fans,

I realized that I often encounter the same situation in a relation
database context, where I really don't know what to do. Here is an
example, where I have 2 tables as follow:


 ____________________
____
__________________                    | PortfolioTitle
|
| Portfolio                  |
+----------------------------------------+
+-----------------------------+                    | tfolio_id  (int)
|
| folio_id  (int)             |<<-PK----FK--| tfolio_idfolio  (int)
|
| folio_name (varchar) |                    | tfolio_idtitle  (int)
|--FK----PK->>[ Titles]
+-----------------------------+                   | tfolio_weight
(decimal(6,5))   |

+-----------------------------------------+


Note that I also have a "Titles" tables (hence the tfolio_idtitle
link).

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

For now, the only way I've found to do this is delete all the entries
of the related folio (e.g.: DELETE TitrePortefeuille WHERE idFolio =
2), and then insert new values for each entry based on the new given
values.

Is there a way to better manage this by detecting which value has to be
inserted/updated/deleted?

And this applies to many situation :(

If you need other examples, I can give you.

thanks a lot!

ibiza

errr....I realized the diagram is all messed up :\

here it is : http://img156.imageshack.us/img156/5096/model8al.gif

errr....I realized the diagram is all messed up :\

here it is : http://img156.imageshack.us/img156/5096/model8al.gif

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

Hi Hugo,

thank you very much for your excellent reply.
Is there a big performance difference between the two methods? I guess
the only difference is that the identity numbers increment a lot
quicker with the first method of deleting a batch then reinserting a
batch, even if only a few records differ.

If I have to run this kind of update quite frequently (once/twice a
day, with thousands of records), what method would you suggest me?

Thanks again! :)

ibiza

On 16 Feb 2006 12:54:19 -0800, ibiza wrote:

>Hi Hugo,
>
>thank you very much for your excellent reply.
>Is there a big performance difference between the two methods? I guess
>the only difference is that the identity numbers increment a lot
>quicker with the first method of deleting a batch then reinserting a
>batch, even if only a few records differ.

Hi ibiza,

Why would you even want to have an identity on the linking table
FolioTitles? Are there many other tables that refer to rows in this
table? I don't think so, since that would make deleting and re-inserting
rows a very bad idea!

If not, then stick to using only the natural key (FolioID + TitreID) and
leave the identity column out.

>
>If I have to run this kind of update quite frequently (once/twice a
>day, with thousands of records), what method would you suggest me?

If performance matters, than you should test both versions on your
hardware, with your data. That's the only way to get relevant data.

(My recommended test procedure is: clear cache; run one method a few
times, then clear cache again and run second method the same number of
times. Do thin on a test system that is as much as possible like your
production system. Compare average execution times).

--
Hugo Kornelis, SQL Server MVP

ibiza (lambertb@gmail.com)  writes:
> thank you very much for your excellent reply.
> Is there a big performance difference between the two methods? I guess
> the only difference is that the identity numbers increment a lot
> quicker with the first method of deleting a batch then reinserting a
> batch, even if only a few records differ.

Why is there an IDENTITY columns at all? Surely a pair of
 (foliotitle_idfolio,
 foliotitle_idtable) can only appear once in table?
Then this should be the primary key, and not any identity column.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Yes, you are both right, I will remove it then.

I thought it was always a good idea to have an identity primary key on
any table.

Thanks for the tip!

ibiza (lambertb@gmail.com)  writes:
> Yes, you are both right, I will remove it then.
>
> I thought it was always a good idea to have an identity primary key on
> any table.

If you ask some people, they will tell you that it is never a good idea!

Personally, I say it is a good idea when there is no useful natural key,
which often is the case for base entities: customers, financial
instruments, addresses etc. However for connection table, or tables
describing composed entities, articifical keys are usually not needed.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx