By using the Query Analyzer, I created 2 tables as follows:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ABC](
[VouNo] [int] IDENTITY(1,1) NOT NULL,
[VouDate] [datetime] NULL,
[printed] [bit] NULL,
[DeleteFlag] [bit] NULL,
CONSTRAINT [PK_ABC] PRIMARY KEY CLUSTERED
(
[VouNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ABC] ADD CONSTRAINT [DF_ABC_printed] DEFAULT ((0)) FOR [printed]
GO
ALTER TABLE [dbo].[ABC] ADD CONSTRAINT [DF_ABC_DeleteFlag] DEFAULT ((0)) FOR [DeleteFlag]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[XYZ](
[VouNo] [int] NOT NULL,
[MemCode] [int] NULL,
[VouDate] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[XYZ] WITH CHECK ADD FOREIGN KEY([VouNo])
REFERENCES [dbo].[ABC] ([VouNo])
GO
Then inserted some records in both tables created above and the statements are:
INSERT INTO ABC(VouDate) Values('07/20/2012')
INSERT INTO ABC(VouDate) Values('07/20/2012')
INSERT INTO ABC(VouDate) Values('07/21/2012')
INSERT INTO ABC(VouDate) Values('07/22/2012')
GO
Insert Into XYZ(VouNo,MemCode,VouDate) Values(1,1001,'07/20/2012')
Insert Into XYZ(VouNo,MemCode,VouDate) Values(2,1002,'07/20/2012')
Insert Into XYZ(VouNo,MemCode,VouDate) Values(1,1001,'07/20/2012')
Insert Into XYZ(VouNo,MemCode,VouDate) Values(2,1002,'07/20/2012')
Insert Into XYZ(VouNo,MemCode,VouDate) Values(3,1003,'07/21/2012')
Insert Into XYZ(VouNo,MemCode,VouDate) Values(1,1001,'07/20/2012')
Insert Into XYZ(VouNo,MemCode,VouDate) Values(3,1003,'07/21/2012')
Insert Into XYZ(VouNo,MemCode,VouDate) Values(2,1002,'07/20/2012')
Insert Into XYZ(VouNo,MemCode,VouDate) Values(4,1004,'07/22/2012')
Insert Into XYZ(VouNo,MemCode,VouDate) Values(2,1002,'07/20/2012')
Insert Into XYZ(VouNo,MemCode,VouDate) Values(4,1004,'07/22/2012')
GO
After adding records in both the tables, the result looks like this:
Result of table ABC
VouNo VouDate printed deleteflag
1 2012-07-20 00:00:00.000 0 0
2 2012-07-20 00:00:00.000 0 0
3 2012-07-21 00:00:00.000 0 0
4 2012-07-22 00:00:00.000 0 0
Result of table XYZ
VouNo MemCode VouDate
1 1001 2012-07-20 00:00:00.000
2 1002 2012-07-20 00:00:00.000
1 1001 2012-07-20 00:00:00.000
2 1002 2012-07-20 00:00:00.000
3 1003 2012-07-21 00:00:00.000
1 1001 2012-07-20 00:00:00.000
3 1003 2012-07-21 00:00:00.000
2 1002 2012-07-20 00:00:00.000
4 1004 2012-07-22 00:00:00.000
2 1002 2012-07-20 00:00:00.000
4 1004 2012-07-22 00:00:00.000
The first execution result using the Select statement is for MemCode 1001 and it is:
SELECT ABC.VouNo,XYZ.MemCode From ABC INNER JOIN XYZ on XYZ.vouno = ABC.vouno where
XYZ.memcode=1001 and ABC.voudate>='07/20/2012' And ABC.voudate<='07/30/2012'
and
ABC.Printed=0 and ABC.deleteflag = 0
The result looks like this:
VouNo MemCode
1 1001
1 1001
1 1001
Now we want to do an update in this transaction of MemCode 1001 such that "Printed" is set to 1 and for that we write the Update statement like this:
UPDATE ABC Set Printed = 1 From ABC INNER JOIN XYZ on XYZ.vouno = ABC.vouno where
XYZ.memcode=1001 and ABC.voudate>='07/20/2012' And ABC.voudate<='07/30/2012'
and
ABC.Printed=0 and ABC.deleteflag = 0
All records are updated when you execute this query with Printed = 1 in the table ABC.
Happy Coding!