[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

"The multi-part identifier "Users.RecID" could not be bound" error on simple update

Claire

3/23/2007 3:21:00 PM

"Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Users.RecID" could not be bound."

I'm in the process of changing my database. Im introducing a SiteID value in
addition to UserID in tables that need it so we can have multi site with a
central server with synchronization.
There's always been Site and User tables but SiteID has only been stored in
a field in the user table to show which site the user belongs to. I now need
to insert SiteID in any table that stores the UserID so that UserID/SiteID
will become primary key in user table.

To "Doctor" I added my fk_sites_RecID field then tried to run a simple query
to update the table. This failed with the error above and I don't understand
what's wrong.
(fk_sites_recID hasnt been set as part of the primary key on the users table
yet)

"update doctor
set Doctor.fk_sites_RecID = Users.fk_sites_recID
where (Users.RecID = doctor.fk_users_recID)"

thank you
3 Answers

Claire

3/23/2007 3:35:00 PM

0

I got the following query working but i dont know why the original failed.
Can someone explain please?

update doctor
set Doctor.fk_sites_RecID = Users.fk_sites_recID
from users where (Users.RecID = doctor.fk_users_recID)


Daniel Crichton

3/23/2007 3:42:00 PM

0

Claire wrote on Fri, 23 Mar 2007 15:20:51 -0000:

> "Msg 4104, Level 16, State 1, Line 1
> The multi-part identifier "Users.RecID" could not be bound."
>
> I'm in the process of changing my database. Im introducing a SiteID value
> in addition to UserID in tables that need it so we can have multi site
> with a central server with synchronization.
> There's always been Site and User tables but SiteID has only been stored
> in a field in the user table to show which site the user belongs to. I now
> need to insert SiteID in any table that stores the UserID so that
> UserID/SiteID will become primary key in user table.
>
> To "Doctor" I added my fk_sites_RecID field then tried to run a simple
> query to update the table. This failed with the error above and I don't
> understand what's wrong.
> (fk_sites_recID hasnt been set as part of the primary key on the users
> table yet)
>
> "update doctor
> set Doctor.fk_sites_RecID = Users.fk_sites_recID
> where (Users.RecID = doctor.fk_users_recID)"
>
> thank you
>


In that update statement there is no Users table referenced other than in
the where. Try this:

update Doctor
set Doctor.fk_sites_RecID = Users.fk_sites_recID
from Doctor inner join Users on Users.RecID = Doctor.fk_users_recID

As you can see, both tables are in the FROM clause using an INNER JOIN, with
the update refering to just the Doctor table.

Dan


Plamen Ratchev

3/23/2007 3:44:00 PM

0

There seems to be no reference to table Users in your query. Maybe something
like this:

UPDATE Doctor
SET fk_sites_RecID = (SELECT fk_sites_RecID
FROM Users AS U
WHERE U.RecID = Doctor.fk_users_RecID)
WHERE EXISTS (SELECT *
FROM Users AS U1
WHERE U1.RecID = Doctor.fk_users_RecID)

or a more SQL Server flavor:

UPDATE Doctor
SET fk_sites_RecID = U.fk_sites_RecID
FROM Users AS U
WHERE Doctor.fk_users_RecID = U.RecID

HTH,

Plamen Ratchev
http://www.SQL...