Record lost with UPDATE statements

[Nipon]

Hi,
I am using MS SQL Server 7.0 SP2 in Windows 2000 server SP4.

I have one-to-many tables (TABLE_HEAD and TABLE_DETAILS)which I am
going to update by using a stored procedure with UPDATE statements.

But somehow ,ONCE IN A WHILE, when executing the stored procedure
with about 1000 rows updated, I lost 10-20 records from TABLE_HEAD
(seems like 10-20 records were deleted) , and all data rows in
TABLE_DETAILS were updated correctly (even details of lost rows of
TABLE_HEAD).

In update procedure, I update both part of primary key and other
columns with having WHERE condition.

Please help , I really don't know why this happens.

Thanks in advance
Nipon Wongtrakul

[David Portas]

Have you checked if there are any triggers on the table you are updating?
Test any trigger code to see if it is handling updates of the primary key
columns correctly.

--
David Portas
SQL Server MVP
--

[John Bell]

Hi

Using a surrogate key will remove the need to update the details table.
Posting DDL (Create table etc) and example data (as insert statements) along
with the statements you are using may help to highlight other problems.

Also once using profiler may show something being missed.

John

"Nipon" <ni****@yahoo.com> wrote in message
news:4c**************************@posting.google.c om...

Hi,
I am using MS SQL Server 7.0 SP2 in Windows 2000 server SP4.

I have one-to-many tables (TABLE_HEAD and TABLE_DETAILS)which I am
going to update by using a stored procedure with UPDATE statements.

But somehow ,ONCE IN A WHILE, when executing the stored procedure
with about 1000 rows updated, I lost 10-20 records from TABLE_HEAD
(seems like 10-20 records were deleted) , and all data rows in
TABLE_DETAILS were updated correctly (even details of lost rows of
TABLE_HEAD).

In update procedure, I update both part of primary key and other
columns with having WHERE condition.

Please help , I really don't know why this happens.

Thanks in advance
Nipon Wongtrakul

[--CELKO--]

>> I have one-to-many tables (TABLE_HEAD and TABLE_DETAILS)which I am
going to update by using a stored procedure with UPDATE statements. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

But somehow ,ONCE IN A WHILE, when executing the stored procedure with about 1000 rows updated, I lost 10-20 records [sic] from
TABLE_HEAD (seems like 10-20 records [sic] were deleted), and all data
rows in TABLE_DETAILS were updated correctly (even details of lost
rows of TABLE_HEAD). <<

If there is no header for a set of details, then the ON DELETE CASCADE
should have removed them for you. Likewise, the ON UPDATE CASCADE
action should have done some of the work for you between the PK-FK.
In update procedure, I update both part of primary key and other

columns with having WHERE condition. <<

We need to see code to debug it. It could be:

1) If you use a locator like IDENTITY as a key, and then update the
natural key, you can get the relationships out of synch.

2) There is a TRIGGER doing strange things.

3) The updates are not in the same transaction

4) The UPDATE has a FROM or other proprietary clause that does strange
things.

5) Something else.