how can i delete,update data from two tables using CTE?

Posted by Sudheep.grandhe under Sql Server on 1/11/2012 | Points: 10 | Views : 7036 | Status : [Member] | Replies : 13

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi,

I need CTE query for the update,delete operations.how can i update,delete operations using two tables.the two table are tbl_report1,tbl_report2.

tbl_report2 columns are:invoiceno(PK),companyname,companyaddress,orderno,ondate,invoicedate,amountstatus,netvat,netamount,tamount
tbl_report1 columns are:invoiceno(Fk),particulars,quantity,perprice,amount,orderno,invoicedate,ondate,companyname,invoiceid.

how can i do this.please do the needful.

Best,
Sudheep.

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Sksamantaray on: 1/11/2012 [Member] Silver | Points: 25

0	what is CTE? Thanks, Sanjay Sudheep.grandhe, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sudheep.grandhe on: 1/11/2012 [Member] Starter | Points: 25

0	Hi, CTE means common table expression Best, Sudheep. Sudheep.grandhe, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sksamantaray on: 1/11/2012 [Member] Silver | Points: 25

0	sorry i have no idea on this!! however i will send u a sollution for this after sometime Thanks, Sanjay Sudheep.grandhe, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sudheep.grandhe on: 1/11/2012 [Member] Starter | Points: 25

0	Hi, thanks for replying to me .other than cte how can i join the two tables for deleting or updating. Best, Sudheep. Sudheep.grandhe, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sksamantaray on: 1/11/2012 [Member] Silver | Points: 25

0	create table t1(eno int,name varchar(200)) create table t2(id int,eno int,salary int) create procedure spDeleteEmp @eno int as begin -- before delete emp detail check the existence of any child record in salary @ctr int select @ctr=COUNT() from t2 where eno=@eno if(@ctr>0) begin return end if(@ctr=0) begin --go ahead with delete operation here delete from t1 where eno=@eno end end Thanks, Sanjay Sudheep.grandhe, if this helps please login to Mark As Answer*. \| Alert Moderator


create table t1(eno int,name varchar(200))

create table t2(id int,eno int,salary int)



create procedure  spDeleteEmp

@eno int

as 

begin

-- before delete emp detail check the existence of any child record in salary

@ctr int

select @ctr=COUNT(*) from t2 where eno=@eno 

if(@ctr>0)

begin



return

end

if(@ctr=0)

begin



--go ahead with delete operation here

delete from t1 where eno=@eno 

end



end

Thanks,
Sanjay

Sudheep.grandhe, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sudheep.grandhe on: 1/11/2012 [Member] Starter | Points: 25

0	Hi, thanks for replying to me .can you post the query for my tables.please do the needful. Best, Sudheep. Sudheep.grandhe, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Ogipansrk on: 1/12/2012 [Member] Starter | Points: 25

0	Hello, You can use MERGE Option for performing updates and deletes in single query. [supported in sql20008] Regards, Ogipansrk Sudheep.grandhe, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sudheep.grandhe on: 1/12/2012 [Member] Starter | Points: 25

0	Hi, Thanks for replying to me.But, iam using sqlserver 2005.any solution for that. Best, Sudheep. Sudheep.grandhe, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Ogipansrk on: 1/12/2012 [Member] Starter | Points: 25

0	Sudheep, why don't u try below.[ update using multiple table] UPDATE T1 SET T1.Particulars ='%%%%' FROM dbo.tbl_Report1 T1 INNER JOIN dbo.tbl_Report1 T2 ON T1.INVOICENO = T2.INVOICENO Ogipansrk similary u can workout for delete too. Regards, Sudheep.grandhe, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sudheep.grandhe on: 1/12/2012 [Member] Starter | Points: 25

0	hi, thanks for replying to me .it shows the following error.Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ','. UPDATE T1 SET T1.quantity,t1.perprice,t1.amount,t1.orderno,t1.invoicedate,t1.ondate from tbl_report1 t1 inner join tbl_report1 t2 on t1.invoiceno=t2.invoiceno Best, Sudheep. Sudheep.grandhe, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Ogipansrk on: 1/12/2012 [Member] Starter | Points: 25

0	why are u using same table in inner join also? Sudheep.grandhe, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sqldev on: 3/11/2013 [Member] Starter | Points: 25

0	Hi, Below video have complete information about CTE like what is CTE , properties of CTE and examples for CTE in sql server. http://www.youtube.com/watch?v=Ly-YqPVdVOk&feature=youtu.be Best Regards, Sql Expert http://learnsqltips.blogspot.com/ Sudheep.grandhe, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Pandians on: 3/12/2013 [Member] [MVP] Silver | Points: 25

0	Check It Out! UPDATE Rep1 SET Rep1.particulars = 'XXXX' FROM tbl_report1 Rep1 JOIN tbl_report2 Rep2 ON (Rep1.invoiceno = Rep2.invoiceno) GO DELETE Rep1 FROM tbl_report1 Rep1 JOIN tbl_report2 Rep2 ON (Rep1.invoiceno = Rep2.invoiceno) WHERE Rep2.invoiceno=100 GO Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Sudheep.grandhe, if this helps please login to Mark As Answer. \| Alert Moderator

Check It Out!

UPDATE Rep1 SET Rep1.particulars = 'XXXX'

FROM tbl_report1 Rep1 JOIN tbl_report2 Rep2

ON (Rep1.invoiceno = Rep2.invoiceno)

GO



DELETE Rep1 FROM tbl_report1 Rep1 JOIN tbl_report2 Rep2

ON (Rep1.invoiceno = Rep2.invoiceno)

WHERE Rep2.invoiceno=100

GO

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Sudheep.grandhe, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts