CODEWITHSUNDEEP
sqlsql-server
user4071182
user4071182

Reputation: 3

Sql Subquery result

I have 3 tables, A, B,C

I want to update table A,column Name with the values from Table B, Column Name, something like:

update A set A.name = 
(select b.name 
 from B where code = c.code) 
where a.latlong = c.latlong

Note that all the columns are not related.

Would appreciate the right direction to go about it.

Have tried Sub queries with inner joins, but no good.

Upvotes: 0

Views: 89

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You can do this with an update using join:

update a
    set name = b.name
    from a join
         c
         on c.latlong = a.latlong join
         b
         on b.code = c.code;

Upvotes: 2

Choudhury Saadmaan Mahmid
Choudhury Saadmaan Mahmid

Reputation: 1316

You have mentioned in your question the following:

I want to update table A,column Name with the values from Table B, Column Name

But what I can see from your query is that actually , you need only those values of the column Name of table B which has same value of code as in table C, and that your latlong in A should be the same as latlong in C, if I'm not mistaken.

Based on that, I can say you need an SQL JOIN operation for Tables B and C with table A. Something like this:

UPDATE A SET A.name = B.Name
FROM A
JOIN C
ON C.latlong = A.latlong
JOIN B
ON B.code = C.code

No need to create a SUBQUERY

Upvotes: 0

user4071182
user4071182

Reputation: 3

Last condition is missing where Table A.Latlong = C.Latlong to pick up the correct code!

Upvotes: 0

Stefan Michev
Stefan Michev

Reputation: 5093

Try update with INNER JOIN 

update A set 
    A.name = B.name
FROM A
INNER JOIN C on C.latlong = A.latlong
INNER JOIN B on B.code = C.code

Upvotes: 1

Related Questions