1

I have two tables ta, tb. ta columns - cId, c1, c2. c1 and c2 contain nulls and need to be filled with data. tb columns - cId, c3, c4. The data for c1 and c2 will come from c3 and c4 respectively.

So, I tried to do a simple inner join first. Both tables were aliased as al_ta and al_tb respectively. Then, I put an update statement - 

UPDATE ta SET 
  al_ta.c1 = al_tb.c3, 
  al_ta.c2 = al_tb.c4
FROM ta AS al_ta
INNER JOIN tb AS al_tb 
ON al_tb.cId = al_tb.cId

This does not work and I get an error - The multi-part identifier al_ta.c1 could not be bound. How do I make this work ?

Sample tables -

ta 

cId c1  c2
1   NULL    NULL
2   NULL    NULL
3   NULL    NULL

tb

cId c3  c4
1   11  111
2   22  222
3   33  333
4   44  444
Trojan.ZBOT
  • 1,398
  • 2
  • 14
  • 23

2 Answers2

2

When referencing the columns, you need to use the alias, not the base table name, if you've abstracted the table names away in the JOIN. Guessing at what your join might look like, you probably meant to write it this way:

UPDATE ta SET 
  ta.c1 = tb.c3, 
  ta.c2 = tb.c4
FROM dbo.some_long_table_name_a AS ta
INNER JOIN dbo.some_long_table_name_b AS tb 
ON ta.cId = tb.cId
WHERE ta.c1 IS NULL OR ta.c2 IS NULL;

I don't understand the purposes of saying:

FROM ta AS al_ta

Why would you bother using an alias here that is actually harder to write than the original table name?

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Yes, thats what similar to what I meant, excluding the where clause since everything is null already. but, i think its better to add that where clause. Adding code and sample data now. – Trojan.ZBOT Nov 26 '13 at 02:00
  • @Trojan.ZBOT I wasn't sure if any of the rows had data in those columns already. If they do, then this will prevent you from updating those rows again; if they don't, this clause isn't going to make your update any worse. – Aaron Bertrand Nov 26 '13 at 02:02
  • Yes, the aliases don't make any sense here. But, in my real DB, they do. The fully qualified names are huge and aliases have to be used. – Trojan.ZBOT Nov 26 '13 at 02:11
  • @Trojan.ZBOT Ok, so in your question you were saying `UPDATE long_table_name SET alias1.col = alias2.col FROM dbo.long_table_name AS alias1 JOIN ...` - you can't do that. You need to say `UPDATE alias1 SET ...`. – Aaron Bertrand Nov 26 '13 at 02:12
  • @AaronBertrand - Can this be considered as a lookup of sorts ? – Steam Dec 19 '13 at 19:05
  • @blasto sorry, I don't quite get your meaning. – Aaron Bertrand Dec 19 '13 at 19:09
  • @AaronBertrand - I mean LOOKUP each cId of ta in tb. If it is present in tb, then move data from columns of tb to ta. – Steam Dec 19 '13 at 19:18
  • @blasto well, logically it's a join that will perform a (hopefully clustered) index update. Whether the query plan to satisfy the join and find the rows ultimately uses an explicit lookup operator depends on a variety of factors. In casual conversation, no, I would not call this a lookup. – Aaron Bertrand Dec 19 '13 at 19:22
0

Please Try it

update ta set 
 ta.c1 = b.c3, 
  ta.c2 = b.c4
from ta a join tb b on a.cid = b.cid
code save
  • 1,054
  • 1
  • 9
  • 15