Solved

Simple MySql Update query gives 1054 error

Posted on 2007-12-02
6
292 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikipedia.org/wiki/PHP  Very powerful.  But a…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

813 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now