0

Facing problem to create SQL query to copy table1 column entry into table2 column entry.

Scenario is,

City is a table with following attributes,

  • City_Id
  • latitude
  • longitude
  • Active

Venue is a table with following attributes,

  • V_id
  • City_Id
  • lat
  • long

I want to copy all the latitude & longitude into lat & long respectively where lat & long should be NULL and when city is Active = 1 and c.city_id = v.city_id. But there are multiple city_id exists in Venue and accordingly I need to copy this.

It will be great help if someone helps me to build a query for this.

Rohit Patil
  • 125
  • 1
  • 11
  • read about `update ... join ... set ... where ...` – Jens Sep 01 '16 at 10:24
  • copy all the latitude & longitude .. where lat & long should be NULL , i suppose – Serg Sep 01 '16 at 10:26
  • Possible duplicate of [How can I do an UPDATE statement with JOIN in SQL?](http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql) – Alexander Guz Sep 01 '16 at 10:30

2 Answers2

1

You can use an UPDATE statement with JOIN

UPDATE Venue
INNER JOIN City ON City.City_Id = Venue.City_Id
SET Venue.lat = City.latitude, Venue.long = City.longitude
WHERE City.Active = 1 AND (Venue.lat IS NULL OR Venue.long IS NULL)
Dimitri L.
  • 4,499
  • 1
  • 15
  • 19
1

Use an UPDATE query with a JOIN

UPDATE Venue AS v
JOIN City AS c ON v.city_id = c.city_id
SET v.lat = c.latitude, v.long = c.longitude
WHERE c.active = 1 AND v.lat IS NULL AND v.long IS NULL
Barmar
  • 741,623
  • 53
  • 500
  • 612