SQLite Forum

I need a little help with UPDATE?
Login

I need a little help with UPDATE?

(1) By David Jackson (davidjackson) on 2021-10-20 18:12:58 [link] [source]

Ok, I think I am close. What is the correct code for my update query?

UPDATE t1 SET t1.start_station_name = s.start_station_name WHERE s.start_lat = t1.start_lat AND s.start_lng = t1.start_lng

TX, David

(2) By David Raymond (dvdraymond) on 2021-10-20 18:39:58 in reply to 1 [link] [source]

Well, you haven't told us what you wanted it to do, or why you think there's a problem. So we have to guess on everything.

For starters as to what's wrong: "s" isn't defined anywhere in there.

(3) By Ryan Smith (cuz) on 2021-10-20 19:13:22 in reply to 1 [link] [source]

David Raymond is right, we need a lot more info to offer anything resembling "wise" advice.

If I have to guess, your most immediate solution to just make it work, regardless of how well it functions (and assuming the "missing" s table in your query is actually called "s"), would be to do:

UPDATE t1 SET t1.start_station_name = (
    SELECT s.start_station_name
      FROM s
     WHERE s.start_lat = t1.start_lat AND s.start_lng = t1.start_lng
    )
;

Note that this will replace ALL station names in t1 with whatever they are in "s" for the same lat-long. Please back up your files before doing this, and give us much more information for more useful suggestions.

Note Also - if lat and lng are REAL (Floating point) fields, the equality check x.lat = y.lat is not guaranteed to work due to small possible differences in float values that seem equal to the naked eye.

(4.1) By Keith Medcalf (kmedcalf) on 2021-10-21 02:38:30 edited from 4.0 in reply to 3 [source]

Note that this will replace ALL station names in t1 with whatever they are in "s" for the same lat-long

and any location not found will have the station name set to null.

Either of the following will fix that (assuming that you do not want not found station names set to null.

UPDATE t1
   SET start_station_name = coalesce((SELECT s.start_station_name
                                        FROM s
                                       WHERE start_lat = t1.start_lat
                                         AND start_lng = t1.start_lng
                                     ), start_station_name)
;
-- or --
UPDATE t1
   SET start_station_name = s.start_station_name
  FROM s
 WHERE s.start_lat = t1.start_lat
   AND s.start_lng = t1.start_lng
;