Solved

Simple MySql Update query gives 1054 error

Posted on 2007-12-02
6
293 Views
Last Modified: 2010-04-21
I've got a table (logentries) that has 2 columns of data , analyte1 and analyte3 and I need to swap the data in SOME of the rows. To determine those rows requires a join of the logentries table. So, in order not to have a subqurery select that depended on the logentries table, I created a table (that I will delete after the swap). The temporary table was created by:

CREATE table LogIds3014
SELECT
    logentries.LogEntryID,
    logentries.Analyte1,
    logentries.Analyte3
FROM
    metricsil.logentries
    INNER JOIN metricsil.productruns
        ON (logentries.ProductRunID = productruns.ProductRunID)
    INNER JOIN metricsil.products
        ON (productruns.ProductID = products.ProductID)
WHERE (products.UnitID =3014);

Ok, no problem, new table is made up with the data I want, so now I go to update my logentries table with the following:

update logentries
    set logentries.Analyte1 = logids3014.Analyte3,
    logentries.Analyte3 = logids3014.Analyte1
where(logentries.LogEntryID=logids3014.LogEntryID);

but instead of working, I get the following error:
Error Code : 1054
Unknown column 'logids3014.LogEntryID' in 'where clause'
(0 ms taken)

I must be doing some bone head mistake, any help would be appreciated.
0
Comment
Question by:tteague
  • 3
  • 2
6 Comments
 
LVL 28

Expert Comment

by:gamebits
ID: 20392765
Unknown column 'logids3014.LogEntryID' in 'where clause'

your table is

LogIds3014

with a capital "I"
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20392800
MySQL is Case sensitive. In addition to gamebits, the I is also uppercase.  Check everywhere else in your SQL as well
0
 

Author Comment

by:tteague
ID: 20393231
Good pick up on the case, but when the query was run to create LogIds3014 is saved the table in all lower case, which I didn't think about until these replies. In any event, changing the case to LogIds3014 or leaving it lower case still gives the error.

On another note, if I set the where cause to say : where(logentries.LogEntryID=70799); It then complains -> Error Code : 1054
Unknown column 'logids3014.Analyte3' in 'field list'

BTW, I'm using SQLyog as a graphical interface for all this.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 25

Expert Comment

by:imitchie
ID: 20393254
Ah. I thought this was on *nix. So it's Windows/MySQL
I think it's

update logentries
from logentries inner join logids3014 on logentries.LogEntryID=logids3014.LogEntryID
set logentries.Analyte1 = logids3014.Analyte3, logentries.Analyte3 = logids3014.Analyte1
0
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 20393264
Actually, try this

update logentries, logids3014
SET logentries.Analyte1 = logids3014.Analyte3, logentries.Analyte3 = logids3014.Analyte1
WHERE logentries.LogEntryID=logids3014.LogEntryID
0
 

Author Closing Comment

by:tteague
ID: 31412236
Great, I was close, just missing the logids3014 in the update parameters, I guess that was not intuitive to me. Thanks.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question