Solved

Simple MySql Update query gives 1054 error

Posted on 2007-12-02
6
290 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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.

Join & Write a Comment

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

760 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