Delete everything before updating the table

Question

Post reply

Delete everything before updating the table

itskumar2004

Default port

Points: 1446
More actions
March 3, 2010 at 1:02 pm

#386427

Hi ,
Here is the update statement:
update set
deletedate=getdate()
from
dbo.A
where
deletedate is null
and UserID in (select UR.UserID from
dbo.B UR
inner join dbo.C R on R.RoleID = UR.RoleID
where UR.DeleteDate is null and RCode in ('SN','CM','DI','MC','ES','IS','SU','FE','RCM'))
Now all i want to do is delete everything which is related to Table A
i.e. i have Tables e,f,g,h,i which has userID columns to join with table A and since i am updating Table A before updating i want to delete corresponding rows in e,f,g,h,i tables.
UserId column is the primary key in Table A and Foreign key in tables e,f,g,h,i ...

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

itskumar2004 Default port Points: 1446 More actions · Answer 1

Hi,

I have created a procedure to do this and created a cursor inside a procedure.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

itskumar2004 (3/3/2010)
Hi ,
Here is the update statement:
update set
deletedate=getdate()
from
dbo.A
where
deletedate is null
and UserID in (select UR.UserID from
dbo.B UR
inner join dbo.C R on R.RoleID = UR.RoleID
where UR.DeleteDate is null and RCode in ('SN','CM','DI','MC','ES','IS','SU','FE','RCM'))
Now all i want to do is delete everything which is related to Table A
i.e. i have Tables e,f,g,h,i which has userID columns to join with table A and since i am updating Table A before updating i want to delete corresponding rows in e,f,g,h,i tables.
UserId column is the primary key in Table A and Foreign key in tables e,f,g,h,i ...

Is there a reaon why you don't just write separate deletes for each table?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

itskumar2004 (3/3/2010)
execute sp_executesql @ExecSql,@ExecSql1,@ExecSql2,@ExecSql3,@ExecSql4,@ExecSql5,@ExecSql6,@ExecSql7,@Execsql8
--print @ExecSql,@ExecSql1,@ExecSql2,@ExecSql3,@ExecSql4,@ExecSql5,@ExecSql6,@ExecSql7

i cant say where the exactly problem lies but you can see where the problem occuring by print "sp_executesql" , see if it can help you.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

itskumar2004 Default port Points: 1446 More actions · Answer 4

Jeff Moden (3/3/2010)

itskumar2004 (3/3/2010)
Hi ,
Is there a reaon why you don't just write separate deletes for each table?

Yes,each procedure i am calling will insert the row into history table and update the table i want to delete..

i.e. tables e,f,g,h,i,j,k has history tables like ehistory,fhistory etc....

itskumar2004 Default port Points: 1446 More actions · Answer 5

Hi ,

I found where i am getting error ,for each and every userid the below set statement is getting more than one row.

i.e. for userid =1 the usertoterritory table is returning more than one row .similarly the other set statements...

can anyone give me any ideas to raplace equal to for the below set statement...

Set @UserToTerritoryID = (select UserToTerritoryID from dbo.tUserToTerritory where UserID=@UserID)

Set @UserToRegionID=(select UserToRegionID from dbo.tUserToRegion where UserID=@UserID)

Set @UserToAreaID=(select UserToAreaID from dbo.tUserToArea where UserID=@UserID)

Set @UserToCorporateGroupID=(select UserToCorporateGroupID from dbo.tUserToCorporateGroup where UserID=@UserID)

Set @UserToBusinessUnitID=(select UserToBusinessUnitID from dbo.tUserToBusinessUnit where UserID=@UserID)

Set @UserToPortfolioID=(select UserToPortfolioID from dbo.tUserToPortfolio where UserID=@UserID)

Set @UserToMarketID=(select UserToMarketID from dbo.tUserToMarket where UserID=@UserID)

Set @UserToChannelID=(select UserToChannelID from dbo.tUserToChannel where UserID=@UserID)

itskumar2004 Default port Points: 1446 More actions · Answer 6

I have done it by creating cursors inside a cursor..

i got it.

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 7

itskumar2004 (3/4/2010)
I have done it by creating cursors inside a cursor..
i got it.

Damn, I felt Jeff almost got a Heart attack :w00t: Are you sure there is no way to write this solution as a set based?

Cheers,

J-F

itskumar2004 Default port Points: 1446 More actions · Answer 8

I don't know i need to look in google or some articles.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

J-F Bergeron (3/4/2010)
itskumar2004 (3/4/2010)
I have done it by creating cursors inside a cursor..
i got it.
Damn, I felt Jeff almost got a Heart attack :w00t: Are you sure there is no way to write this solution as a set based?

Heh... saw that and went out for a couple of beers in hopes that it would cure the sudden blindness imparted to my eyes. 😛 Yowch!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

itskumar2004 (3/5/2010)
I don't know i need to look in google or some articles.

So what have you Googled for, so far?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

itskumar2004 Default port Points: 1446 More actions · Answer 11

Set @UserToTerritoryID = (select UserToTerritoryID from dbo.tUserToTerritory where UserID=@UserID)

i have used select instead of set like and called the variable in a procedure

select @usertoterritoryid=usertoterritoryid

from tusertoterritory

where userid=@userid

@sql='exec '+ 'dbo.procedurename ''' + cast(@usertoterritoryid as varchar(10)) +'''

The procedure updates tusertoterritory table based on @usertoterritoryid.

This time i am not getting any errors ,but the query is not doing what it is supposed to do ,i think its because for each and every userid the table tusertoterritory has more than one usertoterritoryid i.e for userid 1 it has 1,2,3,4,5,6 usertoterritoryid's and it is updating only one usertoterritoryid.

Can you explain in detail why i should not use cursors...

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 12

Cursors tend to be a very slow way of updating or deleting multiple rows. Sometimes there isn't any other way but if there is then not using a cursor is frequently better.

Is it possible to to the updates in the same procedure that you're pulling @usertoterritoryid? If that's the case you can just join onto tusertoterritory.

itskumar2004 Default port Points: 1446 More actions · Answer 13

I am sorry for the late reply...

Anyway, i have replaced the cursors with while loop .

here you go ,

Create procedure procedurename

(

@columnname int,

)

as

Declare @column1ID int;

Declare @Sql nvarchar(200);

Declare @e int;

Declare @f int;

set @f=(select count(*) from tableA where columnname=@columnname and Date is null)

set @e=1;

while (@e<=@f)

begin

select @column1ID=column1ID from table A where columnname=@columnname and Date is null

select @Sql='exec '+ 'dbo.procedurename ''' + ''','''+cast(@column1ID as varchar(50)) + ''''

Execute Sp_executesql @Sql

set @e=@e+1;

End --While

end--stored procedure

this is the while loop i have used in my stored procedure.