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

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

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

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

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

use CTE (Common Table Expression) to solve this issue

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

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

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

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

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

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

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