SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    844
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Select finds a column but the update does not

    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!
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    that's weird...

    could you do a SHOW CREATE TABLE please
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    844
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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
    Convert your dollars into silver coins. www.convert2silver.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    844
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Convert your dollars into silver coins. www.convert2silver.com

  7. #7
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    844
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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!
    Convert your dollars into silver coins. www.convert2silver.com

  8. #8
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,201
    Mentioned
    105 Post(s)
    Tagged
    1 Thread(s)
    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/
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by busboy View Post
    #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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •