Use Update Statement in SQL Using Inner Join

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!
 

Up Next
    Ebook Download
    View all
    Learn
    View all