[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Simple MySql Update query gives 1054 error

Posted on 2007-12-02
6
Medium Priority
?
302 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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
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 1500 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month20 days, 4 hours left to enroll

873 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