please help

I have SQL 2000. I have one table tmpJcrewUpdates and another table
Jcrewupdates.
I have a task that reads a text file and imports all of it into
tmpjcrewupdates. The floowing script I am try to get it to either update
exsistign information in Jcrewupdates if the REF# is already in teh
jcrewupdates table. If not, I want the entire data for that ref# to be
sent to the jcrewupdates table.
For some reason, it isn't updating exsisting records, it just adds new ones
if the ref# isn't already there.
update [JCrewUpdates]
set [do rec'd] = [tmpJCrewUpdates].[do rec'd],[bill of ladin
g #] =
[tmpJCrewUpdates].[Bill of lading #],[container] =
[tmpJCrewUpdates].[container],Vessel=[tmpJCrewUpdates].Vessel,CF
LNY=[tmpJCrewUpdates].CFLNY,ETA=[tmpJCrewUpdates].ETA,LFD=[tmpJC
rewUpdates].LFD,terminal=[tmpJCrewUpdates].terminal,cfs=[tmpJCrewUpd
ates].cfs,Ctns=[tmpJCrewUpdates].Ctns,status=[tmpJCrewUpdat
es].status,[p/u
date]=[tmpJCrewUpdates].[p/u date],[del. date]=[tmpJCrewUpda
tes].[del. date]
From [JCrewUpdates]
Join [tmpJCrewUpdates]
on [tmpJCrewUpdates].[ref#]=[JCrewUpdates].[ref#]
Insert into [JCrewUpdates]([do rec'd],ref#,vessel,[BILL OF LADIN
G
#],cflny,eta,LFD,container,terminal,cfs,
ctns,status,[p/u date],[del.
date])
Select [do rec'd],[ref#],vessel,[bill of lading
#],cflny,eta,lfd,container,terminal,cfs,
ctns,status,[p/u date],[del.
date]
From [tmpJCrewUpdates]
Where NOT Exists (Select * from [JCrewUpdates] Where
[JCrewUpdates].[ref#]=[tmpJCrewUpdates].[ref#])Hi John
These are your queries re-written slighly,
UPDATE j
SET [do rec'd] = t.[do rec'd],
[bill of lading #] = t.[Bill of lading #],
[container] = t.[container],
Vessel=t.Vessel,
CFLNY=t.CFLNY,
ETA=t.ETA,
LFD=t.LFD,
terminal=t.terminal,
cfs=t.cfs,
Ctns=t.Ctns,
status=t.status,
[p/u date]=t.[p/u date],
[del. date]=t.[del. date]
FROM [JCrewUpdates] J
JOIN [tmpJCrewUpdates] t ON t.[ref#]=j.[ref#]
INSERT INTO [JCrewUpdates]([do rec'd],[ref#],vessel,
[bill of lading #],cflny,eta,LFD,container,terminal,
cfs,ctns,status,[p/u date],[del. date])
SELECT t.[do rec'd],t.[ref#],t.vessel,
t.[bill of lading #],t.cflny,t.eta,t.lfd,t.container,t.terminal,
t.cfs,t.ctns,t.status,t.[p/u date],t.[del. date]
FROM [tmpJCrewUpdates] t
LEFT JOIN [tmpJCrewUpdates] t ON t.[ref#]=j.[ref#]
WHERE t.[ref#] IS NULL
I could not see anything that would cause the updates not to happen, if the
following returns any rows then you should get updates;
SELECT t.[do rec'd],t.[ref#],t.vessel,
t.[bill of lading #],t.cflny,t.eta,t.lfd,t.container,t.terminal,
t.cfs,t.ctns,t.status,t.[p/u date],t.[del. date]
FROM [tmpJCrewUpdates] t
JOIN [tmpJCrewUpdates] t ON t.[ref#]=j.[ref#]
If not, you may have some problems with the values in the [ref#] columns
John
"Johnfli" wrote:

> I have SQL 2000. I have one table tmpJcrewUpdates and another table
> Jcrewupdates.
> I have a task that reads a text file and imports all of it into
> tmpjcrewupdates. The floowing script I am try to get it to either update
> exsistign information in Jcrewupdates if the REF# is already in teh
> jcrewupdates table. If not, I want the entire data for that ref# to be
> sent to the jcrewupdates table.
> For some reason, it isn't updating exsisting records, it just adds new one
s
> if the ref# isn't already there.
>
>
> update [JCrewUpdates]
> set [do rec'd] = [tmpJCrewUpdates].[do rec'd],[bill of lad
ing #] =
> [tmpJCrewUpdates].[Bill of lading #],[container] =
> [tmpJCrewUpdates].[container],Vessel=[tmpJCrewUpdates].Vessel,CFLNY=&#
91;tmpJCrewUpdates].CFLNY,ETA=[tmpJCrewUpdates].ETA,LFD=[tmpJCrewUpdates].LF
D,terminal=[tmpJCrewUpdates].terminal,cfs=[tmpJCrewUpdates].cfs,Ctns=[tm
pJCrewUpdates].Ctns,status=[tmpJCrewUpd
ates].status,[p/u
> date]=[tmpJCrewUpdates].[p/u date],[del. date]=[tmpJCrewUp
dates].[del. date]
> From [JCrewUpdates]
> Join [tmpJCrewUpdates]
> on [tmpJCrewUpdates].[ref#]=[JCrewUpdates].[ref#]
> Insert into [JCrewUpdates]([do rec'd],ref#,vessel,[BILL OF LAD
ING
> #],cflny,eta,LFD,container,terminal,cfs,
ctns,status,[p/u date],[de
l. date])
> Select [do rec'd],[ref#],vessel,[bill of lading
> #],cflny,eta,lfd,container,terminal,cfs,
ctns,status,[p/u date],[de
l. date]
> From [tmpJCrewUpdates]
> Where NOT Exists (Select * from [JCrewUpdates] Where
> [JCrewUpdates].[ref#]=[tmpJCrewUpdates].[ref#])
>
>