CODEWITHSUNDEEP
mysqlsql
Idea Project
Idea Project

Reputation: 49

MySql: Error #1242 updating a column with data taken from another table

I need to update a column of a table, currently empty, with the data taken from another table.

I have two tables. The "ar_geozone" table has the following structure

ID -> primary key;
code_countries;
code_city;
name_city;
longitude_city;
latitude_city;
name_countries;
name_regions;
kind_regions.

The "name_regions, kind_regions" columns are empty and I would like to update only the "name_regions" column

The second "regions" table has the following structure:

country;
code;
name;
kind.

All the columns in the table "regions" have records. However, the number of records is lower than those in the table "ar_geozone".

Using the following query

UPDATE ar_geozone ct 
    SET name_regions = 
     (SELECT name FROM regions ag 
      WHERE ag.country LIKE ct.code_countries)

the system gives me the error:

"#1242 - Subquery returns more than 1 row".

How should I modify the query to update the column?

Upvotes: 0

Views: 274

Answers (1)

Wilson
Wilson

Reputation: 329

SQL LIKE Operator fetches more than one row. Which means you are trying to update name_regions with more than one country names.

UPDATE ar_geozone ct 
    SET name_regions = 
     (SELECT GROUP_CONCAT(name SEPARATOR ', ') FROM regions ag 
      WHERE ag.country LIKE ct.code_countries)

It will combine more than one row into comma separated String.

Upvotes: 1

Related Questions