Trouble with Update 1

[sparkbyte]

This is the update

Update dbo.tblTrackingTable
	Set EmployeeID_FK = tblEmployeeNetAccts.EmployeeID_PK
Where 
(
	SELECT	tblEmployeeNetAccts.EmployeeID_PK
	FROM	tblEmployeeNetAccts 
			INNER JOIN tblTrackingTable 
			ON tblEmployeeNetAccts.EmployeeDomainID = tblTrackingTable.EmployeeID
	WHERE	(NOT (tblTrackingTable.EmployeeID IS NULL))
)

The error
[red]
Msg 4145, Level 15, State 1, Line 11
An expression of non-boolean type specified in a context where a condition is expected, near ')'.
[/red]

What I am trying to do is add the EmployeeID_PK to the Tracking Table as a FK and need to update the existing records in the TrackingTable with the Employee's PK.

Here are the table structures I am working with.

Tracking Table

CREATE TABLE [dbo].[tblTrackingTable](
	[TrackingID_PK] [uniqueidentifier] NOT NULL CONSTRAINT [DF_tblTrackingTable_TrackingID_PK]  DEFAULT (newid()),
	[EmployeeID] [varchar](50) NULL,
	[MachineName] [varchar](20) NULL,
	[BoxNumber] [varchar](45) NOT NULL,
	[FileNumber] [varchar](25) NOT NULL,
	[TrackingDate] [datetime] NULL,
	[Reship] [bit] NULL,
	[BoxNumberOriginal] [varchar](50) NULL,
	[EmployeeID_FK] [uniqueidentifier] NULL,
 CONSTRAINT [TrackingID_PK] PRIMARY KEY NONCLUSTERED

Employee Table.

CREATE TABLE [dbo].[tblEmployee](
	[EmployeeID_PK] [uniqueidentifier] NOT NULL CONSTRAINT [DF_tblEmployee_EmployeeID_PK_1]  DEFAULT (newid()),
	[EmployeeFN] [nvarchar](50) NULL,
	[EmployeeMI] [nvarchar](50) NULL,
	[EmployeeLN] [nvarchar](50) NULL,
	[EmployeeDept] [nvarchar](50) NULL,
 CONSTRAINT [PK_tblEmployee] PRIMARY KEY CLUSTERED

Employee Net Accts Table

CREATE TABLE [dbo].[tblEmployeeNetAccts](
	[EmployeeID_PK] [uniqueidentifier] NOT NULL,
	[EmployeeDomainID] [varchar](50) NULL,
	[EmployeeFIPSID] [varchar](50) NULL,
 CONSTRAINT [PK_tblEmployeeNetAccts_1] PRIMARY KEY CLUSTERED 
(
	[EmployeeID_PK] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [EmployeeDomainID] UNIQUE NONCLUSTERED 
(
	[EmployeeDomainID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tblEmployeeNetAccts]  WITH CHECK ADD  CONSTRAINT [FK_tblEmployeeNetAccts_tblEmployee] FOREIGN KEY([EmployeeID_PK])
REFERENCES [dbo].[tblEmployee] ([EmployeeID_PK])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tblEmployeeNetAccts] CHECK CONSTRAINT [FK_tblEmployeeNetAccts_tblEmployee]

As seen here there is a one-to-one cascading relation on the [EmployeeID_PK] field between the Employee tables.

Thanks

John Fuhrman

 

[drlex]

John,
I think it's because

WHERE    (NOT (tblTrackingTable.EmployeeID IS NULL))

should be

WHERE    (tblTrackingTable.EmployeeID IS [b]NOT[/b] NULL)

Boolean is the "NOT <expression>" part of the code.

soi là, soi carré

 

[sparkbyte]

Update dbo.tblTrackingTable
	Set EmployeeID_FK = tblEmployeeNetAccts.EmployeeID_PK
Where 
(
	SELECT	tblEmployeeNetAccts.EmployeeID_PK
	FROM	tblEmployeeNetAccts 
			INNER JOIN tblTrackingTable 
			ON tblEmployeeNetAccts.EmployeeDomainID = tblTrackingTable.EmployeeID
	WHERE	tblTrackingTable.EmployeeID Is Not NULL
)

Error:
[red]
Msg 4145, Level 15, State 1, Line 10
An expression of non-boolean type specified in a context where a condition is expected, near ')'.
[/red]

Don't think it's the Not syntax.

Thanks


Thanks

John Fuhrman

 

[sparkbyte]

This doesn't seem to work either.

Update dbo.tblTrackingTable
	Set EmployeeID_FK = tblEmployeeNetAccts.EmployeeID_PK
Where dbo.tblEmployeeNetAccts.EmployeeDomainID = EmployeeID

Error
[red]
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.tblEmployeeNetAccts.EmployeeDomainID" could not be bound.
[/red]

Thanks

John Fuhrman

 

[gmmastros]

The problem is... you have a subquery in the where clause that doesn't seem to be doing anything.

First, let's look at the subquery as it's written.

    SELECT    tblEmployeeNetAccts.EmployeeID_PK
    FROM    tblEmployeeNetAccts 
            [blue]INNER[/blue] JOIN tblTrackingTable 
            ON tblEmployeeNetAccts.EmployeeDomainID = [blue]tblTrackingTable.EmployeeID[/blue]
    WHERE    [blue]tblTrackingTable.EmployeeID Is Not NULL[/blue]

You are using an inner join so the only rows in the output will be based on the ON clause, which matches on the EmployeeId column of the tracking table. Then you have a where clause for the same column not being NULL. Well... the join will prevent the nulls, so the where clause is not needed in this case.

This, in itself, is not the cause of your problem, it's just an interesting observation.

The real cause of your problem is the way you have your subquery in the where clause (of the outer query).

In psuedo code...

[tt]
Update Table
Set Column = Column
Where
(
Some query
)
[/tt]

The where clause must contain boolean expressions. Where employeeid = 7 (for example). Your subquery is returning a list of employee's but there is no comparison being done.

The query I think you are looking for is...

Update dbo.tblTrackingTable
Set    dbo.tblTrackingTable.EmployeeId_FK = tblEmployeeNetAccts.EmployeeID_PK
From   dbo.tblTrackingTable
       Inner Join dbo.tblEmployeeNetAccts
          On tblEmployeeNetAccts.EmployeeDomainID = tblTrackingTable.EmployeeID

Please study this query to make sure I didn't misunderstand your intent. As always, it's best to make sure you have a good backup of your database before testing an update query.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

Thanks again George.

That was what i was looking for.

All the examples I could find did not show the form you have.


Update dbo.tblTrackingTable
Set dbo.tblTrackingTable.EmployeeId_FK = tblEmployeeNetAccts.EmployeeID_PK
[blue]
From dbo.tblTrackingTable
Inner Join dbo.tblEmployeeNetAccts
On tblEmployeeNetAccts.EmployeeDomainID = tblTrackingTable.EmployeeID[/blue]

This is where the examples I could find either had a where clause or a (Select ...) Had I seen something like your solution..

From sometable Inner Join a.ID = b.ID, I may have been able to noodle it out.

Thanks!!!! and yet another star.


Thanks

John Fuhrman