asked on

Update SQL

I am trying to update the value of a field in one table to be that of another field on the same DB but from another table when the ids are the same.

I will be using the query in other databases so I want to include the database name in it. Here is the query. I get an error that the object is invalid or that the object cannot be bound. I double checked the names and they are correct. But there must be a syntax error:

UPDATE [BDotSostrinImmigration].[dbo.Users]
SET [BDotSostrinImmigration].[dbo.users].[MailCareOf] = [BDotSostrinImmigration].[dbo.zzzBDotAddressesHome].[ResCareOf]
WHERE [BDotSostrinImmigration].[dbo.Users].[trackeruserid] = [BDotSostrinImmigration].[dbo.zzzBDotAddressesHome].[IndivID]
AND [BDotSostrinImmigration].[dbo.zzzBDotAddressesHome].[imported] =1

Open in new window

Mike Eghtebas

change

].[dbo.Users]

to

].[dbo].[Users]

everywhere:

UPDATE [BDotSostrinImmigration].[dbo].[Users]
SET [BDotSostrinImmigration].[dbo].[users].[MailCareOf] = [BDotSostrinImmigration].[dbo].[zzzBDotAddressesHome].[ResCareOf]
WHERE [BDotSostrinImmigration].[dbo].[Users].[trackeruserid] = [BDotSostrinImmigration].[dbo].[zzzBDotAddressesHome].[IndivID]
AND [BDotSostrinImmigration].[dbo].[zzzBDotAddressesHome].[imported] =1

Open in new window

Aleks

ASKER

Part of it seems to work but even thought the fields are there and the names are correct I get this error:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "BDotSostrinImmigration.dbo.zzzBDotAddressesHome.IndivID" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "BDotSostrinImmigration.dbo.zzzBDotAddressesHome.imported" could not be bound.

Aleks

ASKER

I don't understand why this is happening. But I can run this query and it returns the results:

SELECT [BDotSostrinImmigration].[dbo].[zzzBDotAddressesHome].[ResCareOf]
FROM [BDotSostrinImmigration].[dbo].[zzzBDotAddressesHome]

Open in new window

But when I run the update it marks: [BDotSostrinImmigration].[dbo].[zzzBDotAddressesHome].[ResCareOf] in red and says that it cannot be bound ???

This is the query as I have it:

UPDATE [BDotSostrinImmigration].[dbo].[Users]
SET [BDotSostrinImmigration].[dbo].[users].[MailCareOf] = [BDotSostrinImmigration].[dbo].[zzzBDotAddressesHome].[ResCareOf]
WHERE [BDotSostrinImmigration].[dbo].[Users].[trackeruserid] = [BDotSostrinImmigration].[dbo].[zzzBDotAddressesHome].[IndivID]
AND [BDotSostrinImmigration].[dbo].[zzzBDotAddressesHome].[imported] =1

Open in new window

Aleks

ASKER

I even tried something simpler, like this:

UPDATE  dbo.Users
SET     Users.MailCareOf = zzzBDotAddressesHome.ResCareOf
WHERE Users.trackeruserid = zzzBDotAddressesHome.IndivID

Open in new window

But it says that:

Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "zzzBDotAddressesHome.IndivID" could not be bound.

What does that mean ?

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aleks

ASKER

This works !