Select finds a column but the update does not

[busboy]

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!

[r937]

that's weird...

could you do a SHOW CREATE TABLE please

[busboy]

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

[r937]

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

[guelphdad]

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.

[busboy]

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.

[busboy]

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!

[DaveMaxwell]

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/

[r937]

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

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