using inner join on update statement

Question

using inner join on update statement

Leonard Rutkowski

SSCrazy

Points: 2668
More actions
March 11, 2011 at 9:43 am

#235146

Hi All,
I am trying to do an update, using an inner join. It is a self-referencing table, and I need to set a value to null. I have tried a couple of different ways, and they work in SSMS, but, depending on the update statement I use, I get errors when I try to use it in my package.
This is the table definition (and yes, I know this is a bad name. I had no control over it. 🙂 ):
CREATE TABLE [dbo].[function](
[functionid] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](100) NULL,
[returntype] [nvarchar](100) NULL,
[moduleid] [int] NULL,
[successor_functionid] [int] NULL,
[checkedout_username] [varchar](92) NULL,
[checkedin_username] [varchar](92) NULL,
[buildid] [int] NULL,
[branchfrom_buildid] [int] NULL,
[revisionnotes] [varchar](max) NULL,
[functionmasterid] [int] NOT NULL,
[description] [nvarchar](max) NULL,
[archived] [bit] NOT NULL,
[iscurrent] AS (CONVERT([bit],case when [successor_functionid] IS NULL AND [checkedout_username] IS NULL AND [archived]=(0) then (1) else (0) end,(0))),
[codeid] [int] NULL,
CONSTRAINT [PK__function__4316F928] PRIMARY KEY CLUSTERED
([functionid] ASC) )
GO
ALTER TABLE [dbo].[function] WITH CHECK ADD FOREIGN KEY([successor_functionid])
REFERENCES [dbo].[function] ([functionid])
GO
Here is the update:
UPDATE [dbo].[function]
SET [successor_functionid] = NULL
FROM [dbo].[function]
INNER JOIN [dbo].[function] functionParent
ON functionParent.functionid = [function].successor_functionid
WHERE functionParent.functionid = ?
That one gives me the error:
The multi-part identifier "functionParent.functionid" could not be bound.
This update:
UPDATE functionChild
SET [successor_functionid] = NULL
FROM [dbo].[function] functionChild
INNER JOIN [dbo].[function] functionParent
ON functionParent.functionid = functionChild.successor_functionid
WHERE functionParent.functionid = ?
gives me the error:
"Invalid object name 'functionChild'."
I've also tried with a CTE, and gives me a syntax error.
Any help would be much appreciated.
Thanks,
Leonard

Viewing 8 posts - 1 through 7 (of 7 total)

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

Dave23 Hall of Fame Points: 3903 More actions · Answer 1

>> The multi-part identifier "functionParent.functionid" could not be bound.

How is the 'DelayValidation' property on the component that is causing this error set? I've seen situations where late binding of parameters can cause a package to produce an error if DelayValidation is set to false.

Leonard Rutkowski SSCrazy Points: 2668 More actions · Answer 2

it doesn't have delay validation. I know it's on 2008, but this is 2005. there is a ValidateExternalMetadata, that I turned to false, but that didn't help.

Leonard

Anjan Wahwar SSCommitted Points: 1617 More actions · Answer 3

Hi lrutkowski

The way you are writing the script will work fine with SSIS 2008, though in your case i would rather suggest to use Script Task. You can update your function table using script task by consuming variable as read only input for passing argument into your SQL.

Thanks

__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

Anjan Wahwar (3/13/2011)
Hi lrutkowski
The way you are writing the script will work fine with SSIS 2008, though in your case i would rather suggest to use Script Task. You can update your function table using script task by consuming variable as read only input for passing argument into your SQL.
Thanks

As this is pure T-SQL, I disagree with your suggestion; it adds an unnecessary layer of complexity.

I just tried this on my system and it worked fine. Are you using an EXECUTE SQL task to execute the code? What sort of database connection are you using?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Leonard Rutkowski SSCrazy Points: 2668 More actions · Answer 5

I am using an OLE DB Command, in a data flow task. There are some technical and business reasons for not using 2008. In fact, this is the only SSIS package that I have in 2005. All the rest are in 2008. Using a SQL task outside the dataflow causes it's own set of coding issues, and using a Script task is an idea, but I was trying to keep this simple. The connection is SQLNCLI.1 so that may be part of the problem (most of my other connections use the .10 connector).

I know there are ways around this, but I was trying to simplify. part of the data flow is a look up transformation, so I still need to do that to determine if I need to do the delete.

Leonard

chris.stuart SSCarpal Tunnel Points: 4965 More actions · Answer 6

I tried the following and worked, no problems on 2008, and cant see why it wount work on 2005 as well.

UPDATE T1

SET [successor_functionid] = NULL

FROM [dbo].[function] T1

INNER JOIN [dbo].[function] T2

ON T2.functionid = T1.successor_functionid

WHERE T2.functionid = 99999

Leonard Rutkowski SSCrazy Points: 2668 More actions · Answer 7

it works fine on 2008. I don't know why it wouldn't work on 2005. No matter, I was overthinking it, and I don't actually need to do the join, I just need to update the succssor_functionid where it equals my input functionid.

Anyway, thanks for the help.

Leonard