how can i delete,update the data from two tables ?

Posted by Sudheep.grandhe under Sql Server on 1/9/2012 | Points: 10 | Views : 3169 | Status : [Member] | Replies : 11
Hi,

I need a 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/9/2012 [Member] Silver | Points: 25

Up
0
Down


Deleteing a invoice record from child table will not create any issue,
But Before deleting a record in Parent table i.e tbl_report2 first check the same invoiceno in tbl_report1 if any record is found , then
dont delete, rather return it from there and provide user a valid message , something like child record exist , so you cant delete.

Thanks,
Sanjay

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

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

Up
0
Down
Hi,

Thanks for replying to me.can you please provide the query for that

Best,
Sudheep.

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

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

Up
0
Down
Can you quickly put table script of both the table here

Thanks,
Sanjay

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

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

Up
0
Down
Hi,

here is the scripts of the table.please find it.

CREATE TABLE [dbo].[tbl_Report1](
[InvoiceNo] [bigint] NULL,
[Particulars] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Quantity] [float] NULL,
[PerPrice] [float] NULL,
[Amount] [float] NULL,
[OrderNo] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CompName] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InvoiceDate] [datetime] NULL,
[OnDate] [datetime] NULL,
[InvoiceId] [bigint] NULL
) ON [PRIMARY]

GO
USE [Sudheeptest]
GO
ALTER TABLE [dbo].[tbl_Report1] WITH CHECK ADD CONSTRAINT [FK_tbl_Report1_tbl_Report2] FOREIGN KEY([InvoiceNo])
REFERENCES [dbo].[tbl_Report2] ([InvoiceNo])


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_Report2](
[CompName] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CompAddress] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InvoiceNo] [bigint] NOT NULL,
[OrderNo] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OnDate] [datetime] NULL,
[InvoiceDate] [datetime] NULL,
[AmountStatus] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NetAmount] [float] NULL,
[NetVat] [float] NULL,
[TAmount] [float] NULL,
CONSTRAINT [PK_tbl_Report2] PRIMARY KEY CLUSTERED
(
[InvoiceNo] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


Best,
Sudheep.

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

Posted by: Thiyagaa on: 1/10/2012 [Member] Starter | Points: 25

Up
0
Down
use CTE (Common Table Expression) to solve this issue

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

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

Up
0
Down
Hi,

Thanks for replying to me.can you please send the query in CTE format.please do the needful.

Best,
Sudheep.

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

Posted by: Thiyagaa on: 1/11/2012 [Member] Starter | Points: 25

Up
0
Down
http://blog.sqlauthority.com/2009/06/23/sql-server-2005-2008-delete-duplicate-rows/

check this link

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,

my requirment is this.how can i delete,update the data from two tables ? and you are give the reply as use CTE (Common Table Expression) to solve this issue.please reply for your answer in the form of CTE.please do the needful

Best,
Sudheep.

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

Posted by: Niladri.biswas on: 2/8/2012 [Member] Platinum | Points: 25

Up
0
Down
Try with MERGE statement....CTE is for projection operation and not for DML operations

Best Regards,
Niladri Biswas

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

Posted by: Muhsinathk on: 6/21/2012 [Member] Bronze | Points: 25

Up
0
Down

Please try this

UPDATE tbl_Report1 SET tbl_Report1.CompName=tbl_Report2.CompAddress FROM tbl_Report1 JOIN tbl_Report2 ON
tbl_Report1.InvoiceNo=tbl_Report2.InvoiceNo

DELETE T1 FROM tbl_Report1 T1 JOIN tbl_Report2 T2 ON T1.InvoiceNo=T2.InvoiceNo

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

Posted by: Muhsinathk on: 9/20/2012 [Member] Bronze | Points: 25

Up
0
Down
Mark as answer if it helpful to you..That helps others who search the same...

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

Login to post response