>Hi,
>
>I extract data from an Oracle database and load a temporary table named
>OldMines_Temp
>The name of the permanent table is OldMines
>The users are only allowed to update the GPS coordinate fields in OldMines
>The value of a field (ct_coordinates_id) in in the permanent table (OldMines)
>change from 1 to another value,
>indicating the source of the coordinates, when a user update the coordinates
>When I refresh OldMines from OldMines_Temp, the changed coordinates must be
>left intact when the field
>ct_coordinates_id has a value <> 1
>I tried the following sql but it clear the coordinate fields to null when
>ct_coordinates_id <> 1
>
>Update OldMines t1
>set
>t1.t_map_ref = (Select t2.t_map_ref from OldMines_Temp t2 where t1.uq_ref_no =
>t2.uq_ref_no),
>t1.lon = (Select t2.lon from OldMines_Temp t2 where t1.uq_ref_no =
>t2.uq_ref_no and t1.ct_coordinates_id = 1),
>t1.lon_deg_dec = (Select t2.lon_deg_dec from OldMines_Temp t2 where
>t1.uq_ref_no = t2.uq_ref_no and t1.ct_coordinates_id = 1),
>t1.lat = (Select t2.lat from OldMines_Temp t2 where t1.uq_ref_no =
>t2.uq_ref_no and t1.ct_coordinates_id = 1),
>t1.lat_deg_dec = (Select t2.lat_deg_dec from OldMines_Temp t2 where
>t1.uq_ref_no = t2.uq_ref_no and t1.ct_coordinates_id = 1),
>t1.elev = (Select t2.elev from OldMines_Temp t2 where t1.uq_ref_no =
>t2.uq_ref_no and t1.ct_coordinates_id = 1)
>WHERE EXISTS (select 1 from OldMines_Temp t2 where t2.uq_ref_no = t1.uq_ref_no)
Basically, Nols, you are trying to put two separate tasks into one query, one
task is to update all that match on uq_ref_no, the other has the additional
requirement of ct_coordinates_id. Now, I think this can be done even in one
query (although I'm not certain I’ve ever used a subselect within COALESCE, but
I think it ought to work), but a bit more thought is required (the field will
be updated regardless of the value of ct_coordinates_id, but to the same value):
Update OldMines t1
set t1.t_map_ref = (Select t2.t_map_ref from OldMines_Temp t2 where
t1.uq_ref_no = t2.uq_ref_no),
t1.lon = coalesce((Select t2.lon from OldMines_Temp t2 where t1.uq_ref_no =
t2.uq_ref_no and t1.ct_coordinates_id = 1),
t1.lon),
t1.lon_deg_dec = coalesce((Select t2.lon_deg_dec from OldMines_Temp t2
where t1.uq_ref_no = t2.uq_ref_no and t1.ct_coordinates_id = 1),
t1.lon_deg_dec),
t1.lat = coalesce((Select t2.lat from OldMines_Temp t2 where t1.uq_ref_no =
t2.uq_ref_no and t1.ct_coordinates_id = 1),
t1.lat),
t1.lat_deg_dec = coalesce((Select t2.lat_deg_dec from OldMines_Temp t2
where t1.uq_ref_no = t2.uq_ref_no and t1.ct_coordinates_id = 1),
t1.lat_deg_dec),
t1.elev = coalesce((Select t2.elev from OldMines_Temp t2 where t1.uq_ref_no
= t2.uq_ref_no and t1.ct_coordinates_id = 1),
t1.elev)
WHERE EXISTS (select 1 from OldMines_Temp t2 where t2.uq_ref_no = t1.uq_ref_no)
An alternative (and in my opinion a more intuitive) way of doing the same thing
(EXECUTE BLOCK at least exists in Fb 2.5, don't know earlier Fb 2.x versions),
is:
EXECUTE BLOCK AS
BEGIN
Update OldMines t1
set t1.t_map_ref = (Select t2.t_map_ref from OldMines_Temp t2 where
t1.uq_ref_no = t2.uq_ref_no)
WHERE EXISTS (select 1 from OldMines_Temp t2 where t2.uq_ref_no =
t1.uq_ref_no);
Update OldMines t1
set t1.lon = (Select t2.lon from OldMines_Temp t2 where t1.uq_ref_no =
t2.uq_ref_no),
t1.lon_deg_dec = (Select t2.lon_deg_dec from OldMines_Temp t2 where
t1.uq_ref_no = t2.uq_ref_no),
t1.lat = (Select t2.lat from OldMines_Temp t2 where t1.uq_ref_no =
t2.uq_ref_no),
t1.lat_deg_dec = (Select t2.lat_deg_dec from OldMines_Temp t2 where
t1.uq_ref_no = t2.uq_ref_no),
t1.elev = (Select t2.elev from OldMines_Temp t2 where t1.uq_ref_no =
t2.uq_ref_no)
WHERE t1.ct_coordinates_id = 1 AND
EXISTS (select 1 from OldMines_Temp t2 where t2.uq_ref_no =
t1.uq_ref_no);
END
A third option is
EXECUTE BLOCK AS
DECLARE VARIABLE uq_ref_no integer;
DECLARE VARIABLE t_map_ref integer;
DECLARE VARIABLE lon integer;
DECLARE VARIABLE lon_deg_dec integer;
DECLARE VARIABLE lat integer;
DECLARE VARIABLE lat_deg_dec integer;
DECLARE VARIABLE elev integer;
BEGIN
FOR SELECT uq_ref_no, t_map_ref, lon, lon_deg_dec, lat, lat_deg_dec, elev
FROM OldMines_Temp
INTO :uq_ref_no, : t_map_ref, :lon, :lon_deg_dec, :lat, :lat_deg_dec, :elev DO
BEGIN
UPDATE OLD_MINES
SET t_map_ref = :t_map_ref
WHERE uq_ref_no = :uq_ref_no;
UPDATE OLD_MINES
SET lon = :lon,
lon_deg_dec = :lon_deg_dec,
lat = :lat,
lat_deg_dec = :lat_deg_dec,
elev = :elev
WHERE uq_ref_no = :uq_ref_no
AND ct_coordinates_id = 1;
END
END
HTH,
Set
