Getting 4104 error using update and a join

Question

Post reply

Getting 4104 error using update and a join

SQLMajor

Mr or Mrs. 500

Points: 576
More actions
September 8, 2006 at 8:47 am

#170245

I am trying to run the following code:
update HelpDetailsTechDesc
set TechnicalDescription = stg.TechnicalDescription
from stg_HelpDetailsTechDesc stg,
  HelpDetailsTechDesc hdtd
Left join HelpDetails hd on stg.EventName = hd.title
where hd.HelpDetailsID = hdtd.HelpDetailsID and
  hd.HelpTypeID = hdtd.HelpTypeID and
  hd.HelpTypeID = 1
And am getting the following error:
Msg 4104, Level 16, State 1, Procedure iss_HelpDetails_PostUpdates, Line 28
The multi-part identifier "stg.EventName" could not be bound.
The 3 tables involved are:
stg_HelpDetailsTechDesc - it contains the new information to be used as the source information in the update. Relevant columns: EventName and TechnicalDescription.
HelpDetails - Needed to join the stg_HelpDetails to HelpDetailsTechDesc. Relevant columns: HelpDetailsID (PK), HelpTypeID (PK) always 1 in this situation, Title (which matches EventName).
HelpDetailsTechDesc - This is the table to be updated. Relevant columns: HelpDetailsID (PK), HelpTypeID (PK) always 1 in this situation, TechnicalDescription.
Very baffeld

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Hello,

Try this one

update hdtd

set hdtd.TechnicalDescription = stg.TechnicalDescription

from stg_HelpDetailsTechDesc stg,

HelpDetailsTechDesc hdtd

Left join HelpDetails hd on stg.EventName = hd.title

where hd.HelpDetailsID = hdtd.HelpDetailsID and

hd.HelpTypeID = hdtd.HelpTypeID and

hd.HelpTypeID = 1

Thanks

Lucky

SQLMajor Mr or Mrs. 500 Points: 576 More actions · Answer 2

Lucky,

I tried your suggestion and still get the same error message.

Thanks ......... reiny

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

It might be because you are using old a new sql syntax.

try putting joins for all tables (even for the cross joins). That should be fix your error.

SQLMajor Mr or Mrs. 500 Points: 576 More actions · Answer 4

I changed to this:

update hdtd

set hdtd.TechnicalDescription = stg.TechnicalDescription

from stg_HelpDetailsTechDesc stg

Left join HelpDetails hd on stg.EventName = hd.title

left join HelpDetailsTechDesc hdtd on hdtd.HelpDetailsID = td.HelpDetailsID

where hd.HelpTypeID = hdtd.HelpTypeID and hd.HelpTypeID = 1

And now get this:

Msg 4104, Level 16, State 1, Procedure iss_HelpDetails_PostUpdates, Line 28

The multi-part identifier "td.HelpDetailsID" could not be bound.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Your version of the query is pretty much an inner join in disguise. Try this in case it works ?!?!

update hdtd

set hdtd.TechnicalDescription = stg.TechnicalDescription

from stg_HelpDetailsTechDesc stg

Left join HelpDetails hd on stg.EventName = hd.title AND hd.HelpTypeID = hdtd.HelpTypeID and hd.HelpTypeID = 1

left join HelpDetailsTechDesc hdtd on hdtd.HelpDetailsID = td.HelpDetailsID

SQLMajor Mr or Mrs. 500 Points: 576 More actions · Answer 6

I changed the command to this:

update hdtd

set hdtd.TechnicalDescription = stg.TechnicalDescription

from stg_HelpDetailsTechDesc stg

left join HelpDetails hd on stg.EventName = hd.title

left join HelpDetailsTechDesc hdtd on hdtd.HelpDetailsID = td.HelpDetailsID and

hd.HelpTypeID = hdtd.HelpTypeID

And now I get this:

Msg 4104, Level 16, State 1, Procedure iss_HelpDetails_PostUpdates, Line 28

The multi-part identifier "td.HelpDetailsID" could not be bound.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Ninja's_RGR'us

SSC Guru

Points: 294069

September 8, 2006 at 12:22 pm

#659429

Can we see the full procedure??

SQLMajor Mr or Mrs. 500 Points: 576 More actions · Answer 8

Here is the full procedure:

IF OBJECT_ID('ss_HelpDetails_PostUpdates') IS NOT NULL

DROP PROCEDURE dbo.ss_HelpDetails_PostUpdates

go

-- exec ss_HelpDetails_PostUpdates

CREATE PROCEDURE dbo.ss_HelpDetails_PostUpdates

AS

-- Copyright (c)

BEGIN

SET NOCOUNT ON

DECLARE @RunTime datetime

select @RunTime = getdate()

update HelpDetails

set CreatedAt = s.CreatedAt,

Spreading = s.Spreading,

Damage = s.Damage,

VirusType = s.VirusType,

Symptom = s.Symptom,

Alias = s.Alias,

RemovalInstructions = s.RemovalInstructions,

RemovalLink = s.RemovalLink,

LastModifiedAt = @RunTime

from stg_HelpDetails s,

HelpDetails hd

where hd.title = s.EventName and

hd.HelpTypeID = s.HelpTypeID

update hdtd

set hdtd.TechnicalDescription = stg.TechnicalDescription

from stg_HelpDetailsTechDesc stg

left join HelpDetails hd on stg.EventName = hd.title

left join HelpDetailsTechDesc hdtd on hdtd.HelpDetailsID = td.HelpDetailsID and

hd.HelpTypeID = hdtd.HelpTypeID

--DELETE stg_HelpDetailsTechDesc

-- from stg_HelpDetailsTechDesc stg

-- left join HelpDetails hd on stg.EventName = hd.title

-- Where hd.lastModifiedAt = @RunTime

--DELETE stg_HelpDetails

-- from stg_HelpDetails stg

-- left join HelpDetails hd on stg.EventName = hd.title

-- Where hd.lastModifiedAt = @RunTime

END

GO

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Ninja's_RGR'us

SSC Guru

Points: 294069

September 8, 2006 at 12:39 pm

#659432

I got nothing to offer !

SQLMajor Mr or Mrs. 500 Points: 576 More actions · Answer 10

It was a simple typo.

The td. should be hd.

Thanks for all who helped!

Ron-279530 Ten Centuries Points: 1381 More actions · Answer 11

A quick thank you to those who posted help here, I was able to resolve a similar issue with the advice listed here and a search of the forums!