Implicit MySQL Join on Update Statement - 0 rows affected

I'm trying to get this MySQL code to work, but it's saying 0 rows affected.

UPDATE assessments, assessment_types
SET assessments.assessment_type_id = assessment_types.id
WHERE (assessment_types.description = "Skills Assessment" AND assessments.id = 2);

Basically I have assessment_types with id and description column, and I just have the id in the assessments.assessment_type_id

I need to update the id.

I searched and couldn't find quite what I need for this.

Thanks!

Table Data: assessment_types id description 1 Knowledge Assessment 2 Skill Assessment 3 Personal Information 4 Natural Skills

Table Structure:

--
-- Table structure for table `assessments`
--

CREATE TABLE IF NOT EXISTS `assessments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_bin NOT NULL,
  `acronym` varchar(255) COLLATE utf8_bin NOT NULL,
  `assessment_type_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_updated` date NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `assessment_type_id` (`assessment_type_id`),
  KEY `language_id` (`language_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=2385 ;

--
-- Table structure for table `assessment_types`
--

CREATE TABLE IF NOT EXISTS `assessment_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `description` varchar(255) CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=7 ;

Answers


You can try doing an explicit join of the two tables in your UPDATE statement:

UPDATE assessments a
INNER JOIN assessment_types at
    ON a.assessment_type_id = at.id
SET a.assessment_type_id = at.id
WHERE (at.description = "Skills Assessment" AND a.id = 2);

Need Your Help

Typical PHP errors

php html mysql

I ve got 2 problems .

CPD / PMD between projects?

java maven-2 code-duplication pmd similarity

I am rephrasing this question to make it a little more straightforward and easy to understand, hopefully.