AndrewAMO

Oops, sorry guys. I'm apparently running a very old version of MySQL, which is of course why the JOIN doesn't work with UPDATE. Though I did manage to export a joined table and do the update in ms excel. Thanks for all the help, -Andrew

Here is the error message I get if I use any type of join: #1064 - You have an error in your SQL syntax near 'INNER JOIN simpleSamples ON ( simpleSamples.`textualDateTime` = sampleIDS.`textu' at line 2

It may be a version error, though I think I'm running mysql 5.0. I removed the primary keys because I thought it might be the source of the error. Though I have tried the code with them still there. Since the code works using SELECT * FROM instead of the UPDATE, as a last resort, I think I'm going to just export the joined tables and import the result back into the database. I'm also looking into using the REPLACE function. I'll post if this works.

Ok for the create sampleIDS table (4000 entrys): CREATE TABLE `ajk60330DELETE`.`sampleIDS` ( `soilSampleID` int( 11 ) NOT NULL default '0', `locationID` int( 11 ) NOT NULL default '0', `sampleDate` date default NULL , `sampleTime` time default NULL , `textualDateTime` varchar( 255 ) NOT NULL default '', `soilSampleName` varchar( 50 ) NOT NULL default '', `collector` varchar( 50 ) NOT NULL default '', `depthcm` float NOT NULL default '0', `topDepthcm` float default NULL , `samplingConditions` varchar( 255 ) default NULL , `snowCover` char( 1 ) default NULL , `snowDepthcm` float NOT NULL default '0', `referenceDoc` varchar( 50 ) NOT NULL default '', `samplingMethod` text NOT NULL , `owner` varchar( 25 ) NOT NULL default '', `dataset` varchar( 25 ) NOT NULL default '', KEY `samplingConditions` ( `samplingConditions` ) , KEY `textualDateTime` ( `textualDateTime` ) , KEY `soilSampleID` ( `soilSampleID` ) ) TYPE = MYISAM ; For the simpleSamples (50 entrys): CREATE TABLE `ajk60330DELETE`.`simpleSamples` ( `soilSampleID` int( 11 ) NOT NULL default '0', `locationID` int( 11 ) NOT NULL default '0', `sampleDate` date default NULL , `sampleTime` time default NULL , `textualDateTime` varchar( 255 ) NOT NULL default '', `soilSampleName` varchar( 50 ) NOT NULL default '', `collector` varchar( 50 ) NOT NULL default '', `depthcm` float NOT NULL default '0', `topDepthcm` float default NULL , `samplingConditions` varchar( 255 ) default NULL , `snowCover` char( 1 ) default NULL , `snowDepthcm` float NOT NULL default '0', `referenceDoc` varchar( 50 ) NOT NULL default '', `samplingMethod` text NOT NULL , `owner` varchar( 25 ) NOT NULL default '', `dataset` varchar( 25 ) NOT NULL default '' ) TYPE = MYISAM ;

Hi Everyone, I need help figuring out how to update one table with info from another. My code is UPDATE sampleIDS LEFT JOIN simpleSamples ON ( simpleSamples.`textualDateTime` = sampleIDS.`textualDateTime` AND simpleSamples.`samplingConditions` = sampleIDS.`samplingConditions` ) SET sampleIDS.`soilSampleID`=simpleSamples.`soilSampleID` When executing this in phpMyAdmin I get an error LEFT JOIN simpleSamples. If you say SELECT * FROM instead of UPDATE and leave off the SET... stuff it works. Can someone please help me with this error? -Andrew