ERROR on : UPDATE table_1 LEFT JOIN table 2...

[AndrewAMO]

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

[fenway]

That's strange -- the multi-table UPDATE syntax does support any type of JOIN. Post the create table structure for each table.

[AndrewAMO]

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 ;

[fenway]

Works fine in MySQL 4.1, though I notice you don't have any PKs?

[AndrewAMO]

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.

[wickning1]

Are you sure you want to use a LEFT JOIN? soilSampleID is set to NOT NULL so it will error out when the left joined column comes up NULL.

Try an INNER JOIN and see if it does what you want it to. If not, you'll have to convert the NULL to a 0. I'll help you with that part if you ask.

[AndrewAMO]

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

[fenway]

I really have no idea why you're encountering this error at all, though I don't have access to a MySQL 5 box at the moment.

[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