I'd imagine that it's because of the one foreign key which is referencing itself. Before deleting the row, SQL Server has to check that it's not violating integrity, and therefore...

Personally, I'd always go for option one.

It worries me when I see names like EntityType, RecordType etc, since it inevitably leads to problems later.

The most common one is that...

Sorry, the second query should read as :

select

so_id,

business_unit,

max(so_line) 'so_line'

into

#so_tab

from

ps_rf_soln assign

where

person_id is not null and primary_flag = 'Y'

group by

so_id, business_unit

select distinct

A.HH_ORIGIN_LOC ,

A.BUSINESS_UNIT ,

A.SO_ID ,

B.NAME ,

A.HH_COMP_DATE

from

ps_rf_so_hdr as a

join ps_rb_person as...

A couple of potential solutions. Sorry if there are any typos etc, but I obviously have nothing to test this against.

First of all, replacing all but one of the subqueries...

Ahh, now I understand.

So how about writing a DELETE trigger for each table to delete any dependent records. That's a fairly common way to implement cascading.

Just remember to...

You could just do 2 deletes :

delete b

from parent_table as a

join child_table as b on b.col1 = a.col1

where a.col2 = my_value

delete parent_table where col2 = my_value

Bill

Not just an ugly piece of code, but more complex than necessary and too dependent on all strings being of identical format.

Not clever

Use the REPLACE function on table2 to remove the unwanted space eg. :

UPDATE table2

SET str_value = REPLACE(str_value, '] [', ']['

Alternatively, if you want to leave the data unchanged, but still...

Optimise your code, so the query runs more quickly and you don't mind waiting a few milliseconds for the results!

You'd save yourself effort with this query and probably others if you had a 'rebate_valid_from' field in the rebate table.

Your sample data shows everything running conveniently from year-end to year-end,...

I'd suggest modifying as follows :

select Cust_No, Tel_No,

(SELECT COUNT(*) + 1

FROM

WHERE Cust_No = s.Cust_No

AND Tel_No s.Tel_No) AS Seq_No

FROM s

This will give the next available sequence number,...

KIK

I think you're going to have to try both options, perhaps on a subset of your data, before deciding which route to take, since either is obviously valid.

In either case,...

I believe that this is the offending line :

EXEC ('SET @CalcDate = DATEADD(' + @DDPeriodType + ',1,@CalcDate)')

because you're 'exec'ing this statement, it doesn't have visibility of your previously declared...

Just a thought - do you have a DELETE trigger on your underlying table? Perhaps this is trying to reference the table and causing the deadlock.

Regards

Rob

It occurred that a re-designed example might be useful.

Change the AccessProfile table to :

create table AccessProfile (

empno int,

switchno smallint,

switchvalue tinyint )

Sample data :

empno switchno switchvalue

----- -------- -----------

123 1...