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 : 3674 | Status : [Member] | Replies : 13
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.



Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response