使用SELECT WHERE NULL更新

reza 发表于 Mysql

雷扎：

我有SQL查询选择从字面上这样

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

实际上我想更新presenloc表中的数据。我要更新的列是基于该选择的lat_kantor，lng_kantor和jarak，我想更新lat_kantor，lng_kantor或jarak为null的数据

秋名：

直：

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。