multi part identifier could not be bound sql

Question

I get multipart can not be bound error on following query

update nfltx
set 
b.boxno = a.boxno,
b.message = a.message,
b.nameboxno = a.nameboxno,
b.namemsg = a.namemsg,
b.phoneboxno = a.phoneboxno,
b.phonemsg = a.phonemsg

FROM ofltx a JOIN nfltx b 
ON a.ls_fullnam = b.ls_fullnam

but if i remove b from boxno message and all i do not get the error . What is the reason behind this. Thank You using sql server 2008

Use the table alias you have given for `nfltx` (`AS b`) in the `update` section. This should resolve the issue. i think this occurs when you mix table names and there prospective aliases... — MoonKnight, Jan 31 '13 at 09:11
@Killercam - You mean write the query as UPDATE nfltx AS b SET...? I believe that is not allowed. — Raj, Jan 31 '13 at 09:25

score 17 · Accepted Answer · answered Jan 31 '13 at 09:14

A table alias specified in a FROM clause cannot be used as a qualifier in SET column_name. This is not valid:

update nfltx
set 
b.boxno = a.boxno,
b.message = a.message,
b.nameboxno = a.nameboxno,
b.namemsg = a.namemsg,
b.phoneboxno = a.phoneboxno,
b.phonemsg = a.phonemsg

FROM ofltx a JOIN nfltx b 
ON a.ls_fullnam = b.ls_fullnam

To make it work, remove the b. alias from the column name.

update nfltx
set 
boxno = a.boxno,
message = a.message,
nameboxno = a.nameboxno,
namemsg = a.namemsg,
phoneboxno = a.phoneboxno,
phonemsg = a.phonemsg

FROM ofltx a JOIN nfltx b 
ON a.ls_fullnam = b.ls_fullnam

Raj

is there any official documenation that refers to these sort of conventions? — nanospeck, Jun 30 '16 at 10:27

score 9 · Answer 2 · answered Jan 31 '13 at 10:02

What is the reason behind this?

An UPDATE (and DELETE, INSERT) can affect one, and only one, table. You've already identified which table you want to affect here:

update nfltx

Therefore, it doesn't make sense to allow an alias for the left hand side of assignments in the SET clause. They must be columns belonging to the previously identified table.

If the same table is included in the FROM clause multiple times (and it's the table you wish to update), you would need to provide an alias to indicate which instance of the table is to be updated - but you'd provide it (once) in the UPDATE clause rather than in the SET clause.

I like your answer more because you explained why it doesn't work. — MarioDS, Nov 27 '14 at 16:22

score 0 · Answer 3 · edited Aug 13 '16 at 15:16

0

just use

update b

instead of

update nfltx

Man you guys make things too difficult for those that are learning.

edited Aug 13 '16 at 15:16

Undo

25,519
37
106
129

answered Aug 13 '16 at 03:24

OneFiveOne

1
1
3

multi part identifier could not be bound sql

3 Answers3

Linked