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

[ibiza]

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 TitrePortefeuil le 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

[ibiza]

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

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

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

[ibiza]

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

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

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

[Hugo Kornelis]

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

On 16 Feb 2006 10:39:45 -0800, ibiza wrote:
[color=blue]
>Hi SQL fans,[/color]
(snip)[color=blue]
>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
>PortfolioTit le table
>2 ) update the title #2 (weight from 30 to 35)
>3 ) add the title #4 to the folio[/color]

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.idF olio
AND n.idTitre = FolioTitles.idT itre)

-- Step 2: UPDATE rows that have been changed
UPDATE FolioTitles
SET poids =
(SELECT poids
FROM NewFolioTitles AS n
WHERE n.idFolio = FolioTitles.idF olio
AND n.idTitre = FolioTitles.idT itre)
WHERE EXISTS
(SELECT *
FROM NewFolioTitles AS n
WHERE n.idFolio = FolioTitles.idF olio
AND n.idTitre = FolioTitles.idT itre
AND n.poids <> FolioTitles.poi ds)
-- 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

[ibiza]

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

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

[Hugo Kornelis]

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

On 16 Feb 2006 12:54:19 -0800, ibiza wrote:
[color=blue]
>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.[/color]

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.
[color=blue]
>
>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?[/color]

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

[Erland Sommarskog]

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

ibiza (lambertb@gmail .com) writes:[color=blue]
> 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.[/color]

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


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[ibiza]

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

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!

[Erland Sommarskog]

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

ibiza (lambertb@gmail .com) writes:[color=blue]
> 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.[/color]

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@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx