Jeff Moden (2/11/2014)

---

pmadhavapeddi22 (2/11/2014)

---

Jeff,

Sorry for the late reply,

continuation of the above topic...

As you asked, the below is one of the update statements we are using in stored procedure

Update de_norm_buscard_attr

set fax=(select fax from view_a x

where x.contractid in ( select pcm_child.contract_id from table_a pcm

inner join table_b pra on pcm.pcm_role_id=pra.pra_role_id

INNER JOIN table_c y ON pra.p_phn_id=y.p_phn_id

inner join table_d pris on pcm.pcm_role_id = pris.parent_role_instance_id

inner join table_a pcm_child on pris.role_instance_id = pcm_child.pcm_role_id

where (y.lst_updt_dtm >=@lst_updt_tm OR pra.lst_updt_dtm >=@lst_updt_tm))

and x.contractid=de_norm_buscard_attr.contract_id)

where contract_id in ( select pcm_child.contract_id from table_a pcm

inner join table_b pra on pcm.pcm_role_id=pra.pra_role_id

INNER JOIN table_c y ON pra.p_phn_id=y.p_phn_id

inner join table_d pris on pcm.pcm_role_id = pris.parent_role_instance_id

inner join table_a pcm_child on pris.role_instance_id = pcm_child.pcm_role_id

where (y.lst_updt_dtm >=@lst_updt_tm OR pra.lst_updt_dtm >=@lst_updt_tm))

I thought it was going to be something like that. It's actually an illegal form of update and, although it will sometimes work correctly, it will eat the face off of your server when it doesn't.

What's illegal about it is that you're updating a table based on a join and the table being updated isn't explicity named as a table in the FROM clause. You won't fine any examples in Books Online that does such a thing when a JOIN is present in an UPDATE.

Add the de_norm_buscard_attr table to the FROM clause, give it an alias, join it properly with the other table(s), and update the alias instead of the table.

To be sure, I've not checked your code for other problems. I was just looking for the "illegal update" problem which is slower than multple accidental Cartesian products.