Geek asks and answers

Error 1054: Column does not exist ... does it do?

Below are my table structures and foreign keys:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `homework9` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `homework9` ;

-- -----------------------------------------------------
-- Table `homework9`.`employee`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `homework9`.`employee` ;

CREATE  TABLE IF NOT EXISTS `homework9`.`employee` (
  `EmployeeNumber` INT NOT NULL ,
  `FirstName` VARCHAR(15) NULL ,
  `LastName` VARCHAR(15) NULL ,
  `Department` VARCHAR(15) NULL ,
  `Phone` VARCHAR(15) NULL ,
  `Email` VARCHAR(25) NULL ,
  PRIMARY KEY (`EmployeeNumber`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `homework9`.`computer`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `homework9`.`computer` ;

CREATE  TABLE IF NOT EXISTS `homework9`.`computer` (
  `SerialNumber` INT NOT NULL ,
  `Make` VARCHAR(12) NOT NULL ,
  `Model` VARCHAR(24) NOT NULL ,
  `ProcessorType` VARCHAR(24) NULL ,
  `ProcessorSpeed` DECIMAL(3,2) NOT NULL ,
  `MainMemory` VARCHAR(15) NOT NULL ,
  `DiskSize` VARCHAR(15) NOT NULL ,
  PRIMARY KEY (`SerialNumber`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `homework9`.`computer_assignment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `homework9`.`computer_assignment` ;

CREATE  TABLE IF NOT EXISTS `homework9`.`computer_assignment` (
  `EmployeeNumber` INT NOT NULL ,
  `SerialNumber` INT NOT NULL ,
  `DateAssigned` DATETIME NOT NULL ,
  `DateReassigned` DATETIME NULL ,
  PRIMARY KEY (`EmployeeNumber`, `SerialNumber`) ,
  INDEX `fk_computer_assignment_computer1` (`SerialNumber` ASC) ,
  CONSTRAINT `fk_computer_assignment_employee`
    FOREIGN KEY (`EmployeeNumber` )
    REFERENCES `homework9`.`employee` (`EmployeeNumber` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_computer_assignment_computer1`
    FOREIGN KEY (`SerialNumber` )
    REFERENCES `homework9`.`computer` (`SerialNumber` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

The updates I'm trying to run causing the error I have ...

USE homework9;
set sql_safe_updates=0; 

UPDATE employee SET Department = 'marketing' WHERE EmployeeNumber = 9;
UPDATE employee SET phone = '315-999-3344' WHERE LastName = 'rubble';
UPDATE computer SET make = 'Dell', model = 'OptiPlex 980', processortype = 'Intel i3-650',
    processorspeed = 3.20, mainmemory = '4.0 GBytyes', DiskSize = '1.0 TBytes' 
    WHERE SerialNumber = 9871278;
UPDATE computer SET processorspeed = processorspeed + 0.50;
DELETE FROM computer_assignment WHERE EmployeeNumber = 11;
DELETE FROM employee WHERE EmployeeNumber = 11;
DELETE FROM computer_assignment WHERE computer_assignment.EmployeeNumber = employee.EmployeeNumber AND employee.LastName = 'rubble';

set sql_safe_updates=1; 

select * from employee;
select * from computer;
select * from computer_assignment;

The result of this error: Unknown employee column.EmployeeNumber in the where section.

What's happening? Any help is much appreciated!

+3
source share
3 answers
DELETE FROM computer_assignment 
WHERE computer_assignment.EmployeeNumber = employee.EmployeeNumber 
AND employee.LastName = 'rubble';

You did not specify a table employeein the sentence FROM, therefore it is unknown.

Did you mean

DELETE FROM computer_assignment 
WHERE computer_assignment.EmployeeNumber in 
  (select employee.EmployeeNumber from employee WHERE
   employee.LastName = 'rubble')
+3
source 
DELETE FROM computer_assignment 
WHERE computer_assignment.EmployeeNumber = employee.EmployeeNumber
  AND employee.LastName = 'rubble'

The problem is that employeeit is not set anywhere in this query.

DELETE FROM computer_assignment
INNER JOIN employee ON computer_assignment.EmployeeNumber = employee.EmployeeNumber
WHERE computer_assignment.EmployeeNumber = employee.EmployeeNumber
  AND employee.LastName = 'rubble'
+2
source

computer_assignment FROM, .

DELETE FROM computer_assignment WHERE computer_assignment.EmployeeNumber = employee.EmployeeNumber AND employee.LastName = 'rubble';

:

DELETE FROM computer_assignment WHERE computer_assignment.EmployeeNumber IN (SELECT EmployeeNumber FROM employee WHERE LastName = "Rubble")
0

More articles:


All Articles