Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

Simple MySql Update query gives 1054 error

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
tteague
Asked:
tteague
  • 3
  • 2
1 Solution
 
gamebitsCommented:
Unknown column 'logids3014.LogEntryID' in 'where clause'

your table is

LogIds3014

with a capital "I"
0
 
imitchieCommented:
MySQL is Case sensitive. In addition to gamebits, the I is also uppercase.  Check everywhere else in your SQL as well
0
 
tteagueAuthor Commented:
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 Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
imitchieCommented:
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
 
imitchieCommented:
Actually, try this

update logentries, logids3014
SET logentries.Analyte1 = logids3014.Analyte3, logentries.Analyte3 = logids3014.Analyte1
WHERE logentries.LogEntryID=logids3014.LogEntryID
0
 
tteagueAuthor Commented:
Great, I was close, just missing the logids3014 in the update parameters, I guess that was not intuitive to me. Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now