Problem with a NULL field

[habneh]

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,

 

[habneh]

please discard the above post

below is the correct one


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 10001, '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,

 

[monksnake]

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

Those should be Id

<.

 

[habneh]

Thanks Monk

still not working

for the last two columns instead of updating them, it leaves them as NULL

 

[ptheriault]

Does this return any data?

SELECT *
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

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[habneh]

No it is not returning any data

 

[ptheriault]

That's why your not updating that column. Somewhere in that query you join is wrong or doesn't match. Start 1 by 1 removing joins till you find the bad one.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[habneh]

I should keep all them though.

The problem is with the NULL, if one field is Null,
I think I can't equate the Null field


I used 'SET ANSI_NULLS OFF' in my code, it is not helping out

any suggestion how to equate two null fields?

Thanks

 

[ptheriault]

yes use COALESCE or ISNULL.

For example

AND COALESCE(tmp2.VNum,0) = COALESCE(tmp.VNum,0)

If the column is null it will set the value to 0. But if it's not null in both tables you'll it will compare 0 to what ever value is in the other table.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[monksnake]

The date column is the one acting funny.

<.

 

[habneh]

Thank you very much paul


Here id ***** for you

that really works great

thanks