CODEWITHSUNDEEP
mysqlsql
Computer User
Computer User

Reputation: 2869

Update data in one table from data in another table

My table Customers has 3 columns:

ID (primary)   city       country
1              Chicago    USA
2              Chicago    USA
3              New York   USA
4              Paris      France

And other table locations has 2 columns:

city (primary)       country
Chicago              Null (Empty)
New York             Null (Empty)
Paris                Null (Empty)

I created this country column in locations table to update country values from Customers table. Like:

UPDATE locations SET country = (SELECT country FROM customers);

But above query gives error: Subquery returns more than 1 row

How do I do that?

Upvotes: 0

Views: 50

Answers (2)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

I think you need UPDATE using JOIN syntax. Try this -

UPDATE locations L
JOIN Customers C ON C.city = L.city
SET L.COUNTRY = C.COUNTRY;

Upvotes: 1

nbk
nbk

Reputation: 49375

You were very close

UPDATE locations l 
SET 
    country = (SELECT 
            country
        FROM
            Customers
        WHERE
            city = l.city
        LIMIT 1)

CREATE TABLE locations  (
  `city` VARCHAR(25),
  `country` VARCHAR(25)
);

INSERT INTO locations 
  (`city`, `country`)
VALUES
  ('Chicago',              Null),
  ('New York',             Null),
  ('Paris',                Null);

✓

✓

CREATE TABLE Customers  (
  `ID` INTEGER,
  `city` VARCHAR(8),
  `country` VARCHAR(6)
);

INSERT INTO Customers 
  (`ID`, `city`, `country`)
VALUES
  ('1', 'Chicago', 'USA'),
  ('2', 'Chicago', 'USA'),
  ('3', 'New York', 'USA'),
  ('4', 'Paris', 'France');

✓

✓

  <!-- -->
CREATE TABLE locations  (
  `city` VARCHAR(25),
  `country` VARCHAR(25)
);

INSERT INTO locations 
  (`city`, `country`)
VALUES
  ('Chicago',              Null),
  ('New York',             Null),
  ('Paris',                Null);

✓

✓

CREATE TABLE Customers  (
  `ID` INTEGER,
  `city` VARCHAR(8),
  `country` VARCHAR(6)
);

INSERT INTO Customers 
  (`ID`, `city`, `country`)
VALUES
  ('1', 'Chicago', 'USA'),
  ('2', 'Chicago', 'USA'),
  ('3', 'New York', 'USA'),
  ('4', 'Paris', 'France');

✓

✓

  UPdate locations l SET country = (SELECT country FROM Customers WHERE city = l.city LIMIT 1)

✓

SELECT * From locations;

city     | country
:------- | :------
Chicago  | USA    
New York | USA    
Paris    | France

db<>fiddle here

✓

SELECT * From locations;

city     | country
:------- | :------
Chicago  | USA    
New York | USA    
Paris    | France

db<>fiddle here

Upvotes: 0

Related Questions