UpdateMissingCoordinates.sql

/*
Purpose:
- Apply previously validated coordinates to rows whose latitude or longitude is missing.

Safety:
- Changes data only when @ApplyChanges = 1. The default execution rolls back.
- Validation/geocoding should happen outside this update and must not expose API keys in SQL.

Requirements:
- SQL Server 2012 or later.

Customization:
- Replace dbo.Location and dbo.LocationValidationResult with neutral equivalents in your schema.
*/

SET XACT_ABORT ON;

DECLARE @ApplyChanges BIT = 0;

BEGIN TRANSACTION;

UPDATE target
SET
    latitude = validated.latitude,
    longitude = validated.longitude
OUTPUT
    inserted.location_id,
    deleted.latitude AS previous_latitude,
    deleted.longitude AS previous_longitude,
    inserted.latitude AS proposed_latitude,
    inserted.longitude AS proposed_longitude
FROM dbo.Location AS target
JOIN dbo.LocationValidationResult AS validated
    ON validated.location_id = target.location_id
WHERE (target.latitude IS NULL OR target.longitude IS NULL)
  AND validated.is_valid = 1
  AND validated.latitude BETWEEN -90 AND 90
  AND validated.longitude BETWEEN -180 AND 180;

IF @ApplyChanges = 1
    COMMIT TRANSACTION;
ELSE
    ROLLBACK TRANSACTION;