Select finds a column but the update does not

Databases
1

The following select statement works just fine:
select uid, member, memberType from users where uID > 31650;

This update statement does not work:
update users set memberType = member where memberType is null;

Error:
Unknown column, ‘memberType’ in field list.

I’m confused how memberType shows up and yet it says it doesn’t exist in the update. Can someone show me what I’m doing wrong?

Thanks!

2

that’s weird…

could you do a SHOW CREATE TABLE please

3

Here you go Rudy.

CREATE TABLE users (
uID int(8) NOT NULL AUTO_INCREMENT,
IPaddress varchar(15) DEFAULT NULL,
registerDate datetime DEFAULT NULL,
confirmed char(3) DEFAULT ‘No’,
confirmProds int(2) DEFAULT ‘0’,
lastConfirmProd date DEFAULT NULL,
postProds int(2) DEFAULT ‘0’,
lastPostProd date DEFAULT NULL,
lazyProds int(2) DEFAULT ‘0’,
lastLazyProd date DEFAULT NULL,
approved char(3) DEFAULT ‘No’,
visited int(4) DEFAULT ‘0’,
logins int(4) DEFAULT NULL,
last datetime DEFAULT NULL,
lastLogin datetime DEFAULT NULL,
lastTemp datetime DEFAULT NULL,
lastMatch datetime DEFAULT NULL,
searchProds int(2) DEFAULT ‘0’,
lastSearchProd date DEFAULT NULL,
companyName varchar(50) DEFAULT NULL,
companyURL varchar(50) DEFAULT NULL,
firstname varchar(30) DEFAULT NULL,
spouseName varchar(30) DEFAULT NULL,
lastname varchar(30) DEFAULT NULL,
city varchar(30) DEFAULT NULL,
state varchar(30) DEFAULT NULL,
zip varchar(10) DEFAULT NULL,
country varchar(50) DEFAULT NULL,
phone varchar(15) DEFAULT NULL,
password varchar(30) DEFAULT NULL,
reminders int(2) DEFAULT ‘0’,
email varchar(50) DEFAULT NULL,
bouncing varchar(3) NOT NULL DEFAULT ‘No’,
sendMatches varchar(3) NOT NULL DEFAULT ‘Yes’,
searchesStopped int(3) NOT NULL DEFAULT ‘0’,
grandFathered varchar(3) DEFAULT ‘No’,
contact char(3) DEFAULT ‘No’,
contacted int(4) DEFAULT ‘0’,
skills varchar(50) DEFAULT NULL,
googleTopicID int(3) DEFAULT NULL,
interests int(2) DEFAULT NULL,
member varchar(11) DEFAULT NULL,
memberType varchar(11) DEFAULT NULL,
membernumber int(8) DEFAULT NULL,
ourDownline varchar(3) NOT NULL DEFAULT ‘No’,
enrollDate date DEFAULT NULL,
referSource varchar(20) DEFAULT NULL,
referURL mediumtext,
guide int(8) DEFAULT NULL,
sponsorID int(8) DEFAULT NULL,
guideUpdated datetime DEFAULT NULL,
sponsorUpdated datetime DEFAULT NULL,
totalReferrals int(4) DEFAULT ‘0’,
totalSearches int(4) DEFAULT ‘0’,
totalQuestions int(4) DEFAULT ‘0’,
subscription varchar(7) DEFAULT NULL,
wantSilver varchar(3) DEFAULT ‘No’,
timeStamp timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (uID)
) ENGINE=MyISAM AUTO_INCREMENT=31754 DEFAULT CHARSET=latin1

4

nope, nope, a bucket of nope

can’t see anything wrong

try to find a row where membertype is null, grabs its id value, and then try this –

update users set memberType = member where uid = value

5

a) is that the actual update query you are using or is that just an example? Seeing the actual query would be better.
b) are you wrapping the name of the field in single quotes? Single quotes instead of backticks (which actually aren’t necessary) would change it from the name of a column to a string value instead.

6

That is the actual query that I used. The single quotes are in the error message, but the actual update query uses no quotes at all.

Thanks.

7

Rudy, instead of using mySQL Workbench to run the query, I tried it in phpMyAdmin. The error message I got back was slightly different:

#1054 - Unknown column 'memberType ’ in ‘field list’

I went in and verified that there is no extra space or foreign character in the memberType column, so I’m confused where this extra space and A with an accent on top came from.

Thanks!

8

Could it be an encoding issue perhaps? Your server is encoded as UTF-16 and you’re working in ANSI or UTF-8? It looks like it’s translating where it shouldn’t be/

9

that’s a hex C2 appended onto the end of the column name

copy/paste your sql statement into a text editor and display the hex