UPDATE with SELECT WHERE NULL

Question

i have sql query select literally like this

SELECT ad.* from (
SELECT idpresen, presenloc.satkerid, lat, lng, LATITUDE as lat_kantor, LONGITUDE as lng_kantor, (6371000 * ACOS(COS(RADIANS(lat)) * COS(RADIANS(LATITUDE)) * COS(RADIANS(LONGITUDE) - RADIANS(lng)) + SIN(RADIANS(lat)) * SIN(RADIANS(LATITUDE)))) as jarak
FROM presenloc
CROSS JOIN koordinat ON presenloc.satkerid LIKE CONCAT(koordinat.satkerid, '%')
) as ad
inner join ( 
SELECT idpresen, min(6371000 * ACOS(COS(RADIANS(lat)) * COS(RADIANS(LATITUDE)) * COS(RADIANS(LONGITUDE) - RADIANS(lng)) + SIN(RADIANS(lat)) * SIN(RADIANS(LATITUDE)))) as jarak
FROM presenloc 
LEFT JOIN koordinat ON presenloc.satkerid LIKE CONCAT(koordinat.satkerid, '%')
group by idpresen,presenloc.satkerid
 ) as f
 on ad.idpresen = f.idpresen and ad.jarak = f.jarak

actually i want to update data in presenloc table. The column that i want to update is lat_kantor, lng_kantor, and jarak based on that select and i want to update data where whether lat_kantor, lng_kantor, or jarak is null

I don't see any effort to UPDATE anything in your post, and I don't see a WHERE clause in your post either. There should be something there along the lines of `WHERE (colA IS NULL) or (colB IS NULL) or (colC IS NULL)`. — Ken White, Aug 25 '20 at 03:53
yes i'm sorry because i don't know where should i put UPDATE and where should i put WHERE as far as i know i should put WHERE jarak is null at the end line and UPDATE should be in top — Reza, Aug 25 '20 at 04:03
https://stackoverflow.com/questions/63333800/sql-doing-math-find-smallest-value-and-then-insert-into-column# here's where i got the query — Reza, Aug 25 '20 at 04:22

score 1 · Accepted Answer · answered Aug 25 '20 at 04:40

Directly:

UPDATE table_to_update
  JOIN ( your long query ) AS data_for_update USING (PK_column)
SET table_to_update.column1 = data_for_update.column1,
 -- ...
    table_to_update.columnN = data_for_update.columnN
WHERE table_to_update.column1 IS NULL
   -- ...
   OR table_to_update.columnN IS NULL

UPDATE with SELECT WHERE NULL

1 Answers1