#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    6
    Rep Power
    0

    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
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by regrahc
    WHERE actives.`Address`= expireds.`Site_Address`
    the problem is, you cannot insert into the same table you're selecting from

    are you sure you're not actually looking for an UPDATE instead of an INSERT?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    the problem is, you cannot insert into the same table you're selecting from

    are you sure you're not actually looking for an UPDATE instead of an INSERT?
    I'm selecting from Actives, Inserting into Expireds (or trying to????)
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by regrahc
    I'm selecting from Actives, Inserting into Expireds (or trying to????)
    so why would require that
    Code:
    actives.`Address`= expireds.`Site_Address`
    the expireds table cannot have a row with the same address as you're trying to insert, becuase you haven't inserted it yet

    unless, of course, you accept only new rows which have an address that matches one of your existing rows

    which means a mighty specialized table, where all the addresses already have to exist

    can you see the problem that this implies?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo