Home > Software engineering >  MySQL update with subquery and give error #1242 - Subquery returns more than 1 row
MySQL update with subquery and give error #1242 - Subquery returns more than 1 row

Time:08-03

I have one question. I have a table table_histories and look like this

id | id_personel | nip | created_at | updated at

and I have another table table_personels look like this

id | nip | name | created_at | updated_at

I want to update the table of table_histories look like this

UPDATE table_histories 
SET id_personel = (
    SELECT id 
    FROM table_personels 
    WHERE nip = table_histories.nip
)

and when I run it, it give me error

#1242 - Subquery returns more than 1 row.

one data on table_personels can have many histories.

any idea how to solve it?

CodePudding user response:

Highest top 1 add this.

UPDATE table_histories 
SET id_personel = (
    SELECT id 
    FROM table_personels 
    WHERE nip = table_histories.nip
    ORDER BY id 
    LIMIT 1;
)
  • Related