Jump to content

Archived

This topic is now archived and is closed to further replies.

AndrewAMO

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

Recommended Posts

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

Share this post


Link to post
Share on other sites
That's strange -- the multi-table UPDATE syntax does support any type of JOIN. Post the create table structure for each table.

Share this post


Link to post
Share on other sites
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 ;

Share this post


Link to post
Share on other sites
Works fine in MySQL 4.1, though I notice you don't have any PKs?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.