Jump to content


Photo

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


  • Please log in to reply
8 replies to this topic

#1 AndrewAMO

AndrewAMO
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 09 April 2006 - 04:58 AM

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


#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 09 April 2006 - 06:51 PM

That's strange -- the multi-table UPDATE syntax does support any type of JOIN. Post the create table structure for each table.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 AndrewAMO

AndrewAMO
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 09 April 2006 - 11:48 PM

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 ;


#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 10 April 2006 - 03:10 PM

Works fine in MySQL 4.1, though I notice you don't have any PKs?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 AndrewAMO

AndrewAMO
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 10 April 2006 - 04:13 PM

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.

#6 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 10 April 2006 - 06:31 PM

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.

#7 AndrewAMO

AndrewAMO
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 10 April 2006 - 07:03 PM

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


#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 10 April 2006 - 08:51 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#9 AndrewAMO

AndrewAMO
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 11 April 2006 - 02:43 AM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users