By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,509 Members | 1,293 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,509 IT Pros & Developers. It's quick & easy.

Record lost with UPDATE statements

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
--
Jul 20 '05 #2

P: n/a
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

Jul 20 '05 #3

P: n/a
>> 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.
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.