Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2011
    Posts
    8

    Unanswered: Unknown column in where clause

    Hello,

    Error message is:
    Unknown column 'expireds.Site_Address' in 'where clause'

    Am getting this error when executing the following procedure, can't figure out where I'm messing up. DB Mgr (SQL Mgr 2010) is finding the columns (once I type in the table name, a list of columns pops up, and I'm able to select the columns by clicking on them):

    INSERT IGNORE INTO expireds (Parcel_Number, Comments)
    SELECT `Oc Phn`, `Showing Instructions`
    FROM actives
    WHERE actives.`Address`= expireds.`Site_Address`
    ;

    Tables:

    CREATE TABLE `actives` (
    `Prcl#` varchar(20) NOT NULL,
    `Address` varchar(30) NOT NULL,
    `City` varchar(20) default NULL,
    `Zip` varchar(10) default NULL,
    `Status` varchar(5) default NULL,
    `LA Full Name` varchar(30) default NULL,
    `LA Phn` varchar(16) default NULL,
    `Oc Phn` varchar(16) default NULL,
    `Showing Instructions` varchar(255) default NULL,
    PRIMARY KEY (`Address`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;


    CREATE TABLE `expireds` (
    `ID` int(11) NOT NULL auto_increment,
    `First_Owner_First_Name` varchar(30) default '0',
    `First_Owner_Last_Name` varchar(30) default '0',
    `Second_Owner_First_Name` varchar(30) default '0',
    `Second_Owner_Last_Name` varchar(30) default '0',
    `MLS#` varchar(30) default '0',
    `Status` varchar(15) default '0',
    `OMD` date default NULL,
    `FollowUpDate` date default NULL,
    `date_last_checked` date default NULL,
    `Site_Address` varchar(30) default '0',
    `Site_Unit` varchar(10) default '0',
    `Site_City` varchar(20) default '0',
    `Site_State` varchar(2) default '0',
    `Site_Zip` varchar(6) default '0',
    `Mail_Address` varchar(30) default '0',
    `Mail_Unit` varchar(10) default '0',
    `Mail_City` varchar(20) default '0',
    `Mail_State` varchar(2) default '0',
    `Mail_Zip` varchar(6) default '0',
    `Parcel_Number` varchar(20) default '0',
    `E-mail` varchar(40) default NULL,
    `Comments` varchar(9999) default '0',
    `Met` tinyint(1) default '0',
    `MetDate` datetime default NULL,
    `Phone1` varchar(15) default '0',
    `Phone2` varchar(15) default '0',
    `Phone3` varchar(15) default '0',
    `Phone4` varchar(15) default '0',
    `Phone5` varchar(15) default '0',
    `Phone6` varchar(15) default '0',
    `Phone7` varchar(15) default '0',
    `Phone8` varchar(15) default '0',
    `Phone9` varchar(15) default '0',
    `Phone10` varchar(15) default '0',
    `Phone11` varchar(15) default '0',
    `Phone12` varchar(15) default '0',
    `Phone13` varchar(15) default '0',
    `Phone14` varchar(15) default '0',
    `Phone15` varchar(15) default '0',
    `DNC` tinyint(1) default NULL,
    PRIMARY KEY (`ID`),
    UNIQUE KEY `Site_Address` (`Site_Address`),
    UNIQUE KEY `Site_Address_2` (`Site_Address`),
    UNIQUE KEY `Site_Address_3` (`Site_Address`),
    UNIQUE KEY `Site_Address_4` (`Site_Address`),
    UNIQUE KEY `Site_Address_5` (`Site_Address`),
    UNIQUE KEY `Site_Address_6` (`Site_Address`),
    UNIQUE KEY `E-mail` (`E-mail`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    Thank you in advance for any and all help!!!!!

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi,

    the problem here is that your INSERT statement is not correct. It appears you are referencing back to a field in the table you are inserting into.

    If you explain what you are trying to achieve? I think you are looking to is to UPDATE the expireds parcel number and comments from the entries that exist in the actives table based on the site address field.

    If that is the case then you will need to run the following query:

    Code:
    UPDATE expireds
       JOIN actives ON (actives.`Address`= expireds.`Site_Address`)
       SET Parcel_Number = `Oc Phn`, Comments = `Showing Instructions`;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Jun 2011
    Posts
    8
    Ronan, you are The Man!! Thank you sir!

Posting Permissions

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