Use Update Statement in SQL Using Inner Join

By using the Query Analyzer, I created 2 tables as follows:

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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]

ALTER TABLE [dbo].[ABC] ADD CONSTRAINT [DF_ABC_printed] DEFAULT ((0)) FOR [printed]

ALTER TABLE [dbo].[ABC] ADD CONSTRAINT [DF_ABC_DeleteFlag] DEFAULT ((0)) FOR [DeleteFlag]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[XYZ](

[VouNo] [int] NOT NULL,

[MemCode] [int] NULL,

[VouDate] [datetime] NULL

) ON [PRIMARY]

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/21/2012')

INSERT INTO ABC(VouDate) Values('07/22/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(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')

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!