| Subject | Conditional update |
|---|---|
| Author | |
| Post date | 2013-10-01T13:31:37Z |
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)
Regards,
Nols Smit