Update with inner join, in an ole db command

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Update with inner join, in an ole db command

Author

Topic

Leonard Rutkowski
Starting Member

1 Post

Posted - 2011-03-11 : 13:52:53

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

ZZartin
Starting Member

30 Posts

Posted - 2011-03-11 : 18:03:25

Why not just something like this?

UPDATE [dbo].[function] functionChild
SET [successor_functionid] = NULL
WHERE functionChild.successor_functionid = ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources