Hi, I have two simple tables:
CREATE TABLE `new_stations` ( `CD` char(3) default '', `STATION` varchar(17) default NULL, `ICAO` varchar(4) NOT NULL default '', `IATA` varchar(4) default '', `SYNOP` varchar(7) default '', `LAT` varchar(6) default '', `LON` varchar(7) default '', `ELEV` varchar(4) default '', `M` char(1) default '', `N` char(1) default '', `V` char(1) default '', `U` char(1) default '', `A` char(1) default '', `C` char(1) default '', `X` char(1) default '', `CODE` char(2) default '', `ATIS` varchar(22) default NULL, `ATC` varchar(22) default NULL, `PPR` varchar(22) default NULL, `FBO` varchar(22) default NULL, PRIMARY KEY (`ICAO`) ); And CREATE TABLE `phonebin` ( `record` int(11) unsigned zerofill NOT NULL auto_increment, `ICAO` varchar(4) NOT NULL default '', `ATIS` varchar(24) default NULL, `ATC` varchar(24) default NULL, `PPR` varchar(24) default NULL, `FBO` varchar(22) default NULL, `comment` varchar(240) default '', PRIMARY KEY (`record`) ); Phonebin is being populated by web users submitting phone numbers for 'Airport Terminal Information Service' - ATIS, Air Traffic Control, ATC etc. (if you are interested the web submission page is http://activitae.com/airbase/phonebin.htm ) A single web contributor may not provide the full set of phone numbers. Occasionally, I would like to update the new_stations table with data from web contributors. However, I do not want to over-write existing data in new_stations with a null field from phonebin. To amplify, I might have collected and correctly filled new_stations.ATIS from web contributor 1. Web contributor 2 may provide phonebin.ATC data. I want to update new_stations with contributor 2's data from phonebin without nulls overwriting existing new_stations data. I've tried UPDATE new_stations, phonebin set new_stations.ATIS=IFNULL(new_stations.ATIS,phonebin.ATIS), new_stations.ATC=IFNULL(new_stations.ATC,phonebin.ATC), new_stations.PPR=IFNULL(new_stations.PPR,phonebin.PPR), new_stations.FBO=IFNULL(new_stations.FBO,phonebin.FBO) where phonebin.icao=new_stations.icao That worked once, for the very first update, but subsequent runs affect no rows at all. I could use a perl script to first fetch data and decide which row and column to update but there has to be an SQL way - doesn't there? All help appreciated!! Angela (activitae.com is a non-commercial hobby site) -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.15/80 - Release Date: 23/08/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
