Adding SQL Case to provide a default in an Update Query

Question

I am working on a query that will assign users to a specific building based on their lat/long coordinates. The query does work and assigns a building id value to the associated user but I would like to assign a value of "unknown location" if no building exists at the users current location.

Essentially, IF location known SET u.building_id = b.building_id, ELSE SET u.building_id = 'unknown location';

The current query is as follows:

UPDATE USERS u 
JOIN BUILDINGS b ON u.latitude BETWEEN (b.latitude - 0.001) AND (b.latitude + 0.001)
    AND u.longitude BETWEEN (b.longitude - 0.001) AND (b.longitude + 0.001)
SET u.building_id = b.building_id

I am thinking this could be accomplished by adding an SQL cases? How should I approach this?

Change made. Sorry 'bout that ha – David Calvin Dec 16 '14 at 19:04 — David Calvin, Dec 16 '14 at 19:04

score 1 · Answer 1 · edited May 23 '17 at 12:12

You could use CASE like so, with a LEFT JOIN

UPDATE USERS u
LEFT JOIN BUILDINGS b 
  ON u.latitude BETWEEN (b.latitude - 0.001) AND (b.latitude + 0.001)
  AND u.longitude BETWEEN (b.longitude - 0.001) AND (b.longitude + 0.001)
SET u.building_id = 
    CASE 
       WHEN b.building_id IS NULL THEN 'unknown location'
       ELSE b.building_id
    END;

Although personally I would use COALESCE to do the same thing:

UPDATE USERS u
LEFT JOIN BUILDINGS b 
     ON u.latitude BETWEEN (b.latitude - 0.001) AND (b.latitude + 0.001)
     AND u.longitude BETWEEN (b.longitude - 0.001) AND (b.longitude + 0.001)
SET u.building_id = COALESCE(b.building_id, 'unknown location');

Replacing the INNER JOIN with LEFT JOIN will ensure that all users are updated, including those with no matched building (hence the NULL building_id). Noted that if there is more than one match on the lat / long, that the assignment of building will be fairly arbitrary.

SqlFiddle here

score 0 · Answer 2 · answered Dec 16 '14 at 18:52

You can try this:

UPDATE USERS u JOIN BUILDINGS b ON u.latitude BETWEEN (b.latitude - 0.001) AND (b.latitude + 0.001)
AND
u.longitude BETWEEN (b.longitude - 0.001) AND (b.longitude + 0.001)
SET u.building_id = (case when **location known** then u.building_id = b.building_id ELSE u.building_id = 'unknown location' END)

obviously you need to change location known for a logical expression

score 0 · Answer 3 · answered Dec 16 '14 at 18:53

UPDATE USERS u LEFT JOIN BUILDINGS b ON u.latitude BETWEEN (b.latitude - 0.001) AND (b.latitude + 0.001)
AND
u.longitude BETWEEN (b.longitude - 0.001) AND (b.longitude + 0.001)
SET u.building_id = IF( ISNULL(b.building_id), 'unknown', b.building_id);

Adding SQL Case to provide a default in an Update Query

3 Answers3