I have two tables, @tmpTable4 and @ReportTable,
I wrote this script to update the last two fields of table @tmpTable4 i.e. ISDEL and REMARK with the last two fields of @ReportTable
but it is not updating what will be the reason
here is the script
SET ANSI_NULLS OFF
GO
DECLARE @ReportTable TABLE(
[Id] [INT]
,[DOCID] [VARCHAR](400)
,[VNum] [VARCHAR](400)
,[Date] [DATETIME]
,[IDEN] [SMALLINT]
,[IDENDate] [DATETIME]
,[ISDEL] [VARCHAR](100)
,[REMARK] [VARCHAR](1000)
)
DECLARE @tmpTable4 TABLE(
[Id] [INT]
,[DOCID] [VARCHAR](400)
,[VNum] [VARCHAR](400)
,[Date] [DATETIME]
,[IDEN] [SMALLINT]
,[IDENDate] [DATETIME]
,[ISDEL] [VARCHAR](100)
,[REMARK] [VARCHAR](1000)
)
INSERT INTO @ReportTable
SELECT 10001, 'HUUUM', '1.0', NULL, NULL, NULL, 'No', 'IDENDATE is Null'
UNION ALL
SELECT 10001, 'HUUUM', '1.0', NULL, 1, '04/19/2006', 'No', 'IDENDATE is Null'
INSERT INTO @tmpTable4
SELECT 10027, 'HUUUM', '1.0', NULL, NULL, NULL, NULL, NULL
UNION ALL
SELECT 10001, 'HUUUM', '1.0', NULL, 1, '04/19/2006', NULL, NULL
UPDATE @tmpTable4
SET ISDEL = tmp.ISDEL
,REMARK = tmp.REMARK
FROM @tmpTable4 tmp2 INNER JOIN @ReportTable tmp
ON tmp2.ID = tmp.ID
AND tmp2.DOCID = tmp.DOCID
AND tmp2.VNum = tmp.VNum
AND tmp2.[Date] = tmp.[Date]
AND tmp2.IDEN = tmp.IDEN
AND tmp2.IDENDate = tmp.IDENDate
SELECT * FROM @tmpTable4
GO
SET ANSI_NULLS ON
thanks,
I wrote this script to update the last two fields of table @tmpTable4 i.e. ISDEL and REMARK with the last two fields of @ReportTable
but it is not updating what will be the reason
here is the script
SET ANSI_NULLS OFF
GO
DECLARE @ReportTable TABLE(
[Id] [INT]
,[DOCID] [VARCHAR](400)
,[VNum] [VARCHAR](400)
,[Date] [DATETIME]
,[IDEN] [SMALLINT]
,[IDENDate] [DATETIME]
,[ISDEL] [VARCHAR](100)
,[REMARK] [VARCHAR](1000)
)
DECLARE @tmpTable4 TABLE(
[Id] [INT]
,[DOCID] [VARCHAR](400)
,[VNum] [VARCHAR](400)
,[Date] [DATETIME]
,[IDEN] [SMALLINT]
,[IDENDate] [DATETIME]
,[ISDEL] [VARCHAR](100)
,[REMARK] [VARCHAR](1000)
)
INSERT INTO @ReportTable
SELECT 10001, 'HUUUM', '1.0', NULL, NULL, NULL, 'No', 'IDENDATE is Null'
UNION ALL
SELECT 10001, 'HUUUM', '1.0', NULL, 1, '04/19/2006', 'No', 'IDENDATE is Null'
INSERT INTO @tmpTable4
SELECT 10027, 'HUUUM', '1.0', NULL, NULL, NULL, NULL, NULL
UNION ALL
SELECT 10001, 'HUUUM', '1.0', NULL, 1, '04/19/2006', NULL, NULL
UPDATE @tmpTable4
SET ISDEL = tmp.ISDEL
,REMARK = tmp.REMARK
FROM @tmpTable4 tmp2 INNER JOIN @ReportTable tmp
ON tmp2.ID = tmp.ID
AND tmp2.DOCID = tmp.DOCID
AND tmp2.VNum = tmp.VNum
AND tmp2.[Date] = tmp.[Date]
AND tmp2.IDEN = tmp.IDEN
AND tmp2.IDENDate = tmp.IDENDate
SELECT * FROM @tmpTable4
GO
SET ANSI_NULLS ON
thanks,