-1

I would like to update the columns of Table1 with the values from Table2 where postcode value from table1 matches table2.

It must work with multiple rows as some rows have same postcode, latitude and longitute data but different door number.

I am using the statement shown below. I am trying to update table1 with latitude and longitue data from table2 for matching postcode. 

update table1
set latitude = (select latitude from table2 where table1.postcode = table2.postcode);

I am getting error message :

Subquery returned more than 1 value.

Table1 
id Postcode Street          City    Latitude Longitude
1  N76PP    44 Camden Road  London  NULL     NULL
2  N76PP    45 Camden Road  London  NULL     NULL
3  N76PP    46 Camden Road  London  NULL     NULL

Table2
id Postcode Street          City    Latitude Longitude
1  N76PP    44 Camden Road  London  51.5166  -0.052787
2  N76PP    45 Camden Road  London  51.5166  -0.052787
3  N76PP    46 Camden Road  London  51.5166  -0.052787
jarlh
  • 42,561
  • 8
  • 45
  • 63
Ash Giano
  • 1
  • 1

2 Answers2

0

following statement works fine in MySQL:

update table1 t1
inner join table2 t2 on t1.postcode=t2.postcode
set t1.latitude=t2.latitude, t1.some_other_field = t2.zzz

also have a look at SQL update from one Table to another based on a ID match

ms sql server version:

UPDATE
    table1
SET
    table1.latitude=table2.latitude
FROM
    table2
INNER JOIN
    table2
ON 
    table1.postcode = table2.postcode;
Maxim Sagaydachny
  • 2,098
  • 3
  • 11
  • 22
  • In MySQL? It's Microsoft SQL Server's syntax – sepupic Nov 20 '19 at 14:37
  • @sepupic, most likely it works ok with different servers. I stressed on mysql to be on safe side. Also people will come later with similar question but in context of MySQL. So it is happy coincidence that it works ok for both ms-sql and mysql. I used identical statement with MySQL once while trying to revert some old values from backup table. I just updated my own once used statement to match table names and fields specified in question. – Maxim Sagaydachny Nov 20 '19 at 14:47
  • The OP's syntax is SQL Server's syntax and yours syntax is (maybe) MySQL syntax, it won't work on SQl Server – sepupic Nov 20 '19 at 14:54
  • Please give me sql server syntax. Thanks – Ash Giano Nov 20 '19 at 15:18
  • @AshGiano, I believe that Gowtham Belliraj provided you with proper answer. – Maxim Sagaydachny Nov 20 '19 at 15:27
0

The subquery will return multiple records as per your query, you are trying to update one column with multiple records that cannot be done.

In this case you can use JOIN.

update t1 set t1.latitude  = t2.latitude 
from table1 as t1 
inner join table2 as t2 on 
t1.postcode = t2.postcode and t1.street = t2.street
Gowtham Belliraj
  • 56
  • 4