sql updating values according to same variable in another table

Question

I would like to update the values of “area” variable according to the new values of table B

my try:

update a set area=(select area from b)
where a.id=b.id

table A

Id   area
1      N
2      S
3      W
4     W
5     E

table B

Id   area
1      W
2      E

Cristian Lupascu · Answer 1 · 2016-10-04T14:55:29.313

3

This should do it:

update a
  set area = b.area
from a
join b
  on a.id = b.id;

edited Oct 04 '16 at 14:55

answered Oct 04 '16 at 14:23

Cristian Lupascu

@progster it should be without the `a.` Someone had edited my answer, but now I have corrected it. – Cristian Lupascu Oct 04 '16 at 14:38
now the error starts in "from". I am using the proc SQL, that is SQL inside SAS, don't know if it's the issue – progster Oct 04 '16 at 14:50
@progster are your tables really named `a` and `b`? – Cristian Lupascu Oct 04 '16 at 14:54
@progster I tested the query in T-SQL (SQL Server 2012) and it worked fine. I wasn't aware you were using SAS. Perhaps you need to look at this: http://stackoverflow.com/a/24631197/390819 – Cristian Lupascu Oct 04 '16 at 15:00
That is non-standard SQL In standard SQL there is no `join` or `from` clause for an `UPDATE` statement – Oct 04 '16 at 15:00

Navneet Rabadiya · Answer 2 · 2016-10-04T14:50:13.637

1

Try with this

Update a set area= COALESCE((select b.area from b where a.id=b.id),a.area);

edited Oct 04 '16 at 14:50

answered Oct 04 '16 at 14:27

Navneet Rabadiya

this overwrite everything in A, I want to update only what it is in B. now I see in A the values of area present in B and the rest is NULL – progster Oct 04 '16 at 14:37
Try with Update a set area= COALESCE((select b.area from b where a.id=b.id),a.area); – Navneet Rabadiya Oct 04 '16 at 14:49
this works thanks, the only issue is that in real life I have almost 100 variables to update with this criteria of a.id=b.id. could be possible something more flexible? I don't know if it is possible to do this with joins – progster Oct 04 '16 at 14:59
Yes, In any case you have to write that. – Navneet Rabadiya Oct 04 '16 at 15:03

2 Answers2