Complicated updates

SQL Apprentice Question
I am trying to do a stored procedure to delete & demote records. I have a
table with Client/Matter #'s where sometimes the matter number needs to be
deleted and so all matters above need to be demoted. For instance, my
client number is 113245, and I have matters 1, 2, 3, 4, 5, and so on.
Matter #3 needs to be deleted, so we rename 4 to 3, and 5 to 4, etc., etc.

My problem is because of relationships, it's turning out to not be so easy.


I have 3 tables that are set up with this client matter number (don't get me
started on why the tables are set up this way!). Each Table has a client
field and a matter field. Client/Matter is the primary key on tbl1, with a
relationship on tbl2 & 3 that says both of those fields have to match tbl1.


I cannot delete matter 3 from tbl1, because of the foreign keys on tbls 2 &
3. I cannot rename the matter because of the foreign keys. I cannot rename
the matter in tbls2 & 3 to something that doesn't exist in tbl1. I am not
sure how to do this. Can anyone tell me what steps to take to get around
these relationships? Thank you for your help.


Celko Answers
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

The basic answer is simple:


UPDATE Foobar
SET foo_nbr
= (SELECT COUNT (F1.foo_key)
FROM Foobar AS F1
WHERE Foobar.foo_key = F1.fookey
AND F1.item_nbr <= Foobar.item_nbr);