The multi-part identifier could not be bound error

Question

I am trying to copy the values of one column (reg_id) in members_profile to the empty (reg_id)
column in reg_members. On executing this query i am getting the multi-part identifier could not be bound error.

INSERT INTO testing.db1.members_profile(reg_id) 
SELECT reg_id 
from testing.db1.reg_members 
WHERE testing.db1.members_profile.loginname = testing.db1.reg_members.loginname;

What is `testing`? A linked server? A database? If that's supposed to be a four-part name, it's missing the schema (e.g. `dbo`) between `db1` and the table name. And I don't understand - you're trying to insert a new row or update an existing row that matches between the two tables? — Aaron Bertrand, Nov 21 '13 at 08:36
@AaronBertrand testing is the name of my database. I am trying to copy all values of reg_id column in members_profile table to an empty column in reg_members. — user2735206, Nov 21 '13 at 08:41
And so you have a schema named `db1`? If so, that's a very confusing schema name. — Aaron Bertrand, Nov 21 '13 at 08:42
You should think about fixing that, because it's quite misleading. — Aaron Bertrand, Nov 21 '13 at 08:44

score 2 · Accepted Answer · edited May 23 '17 at 12:28

If you're trying to update an existing row (or rows), you should be writing an UPDATE statement, not an INSERT. An INSERT adds rows, an UPDATE updates them.

I believe what you're looking for is an UPDATE based on a JOIN (and you should use table aliases so that you don't have to repeat database.table.column on every single reference and clause):

UPDATE r
  SET r.reg_id = m.reg_id
  FROM testing.db1.members_profile AS m
  INNER JOIN testing.db1.reg_members AS r
  ON m.loginname = r.loginname;

The multi-part identifier could not be bound error

1 Answers1