CODEWITHSUNDEEP
mysqlsqlsql-updatesubquery
Mateusz Gebroski
Mateusz Gebroski

Reputation: 1334

sql update where subquery

My exercise is:

The actor HARPO WILLIAMS was accidentally entered in the actor table as GROUCHO WILLIAMS. Write a query to fix the record.

so I am trying to query it by:

update actor
 set actor.first_name = 'HARPO'
 where actor_id in (
  select actor.actor_id from actor
  where concat(actor.first_name, ' ', actor.last_name) = 'GROUCHO WILLIAMS'
 );

unfortunately error is thrown

You can't specify target table 'actor' for update in FORM clause

How can I fix that?

Upvotes: 1

Views: 95

Answers (3)

Jennkyle
Jennkyle

Reputation: 1

Just match the first name and the last names seperately to update the row. I don't think the subquery is important.

Upvotes: 0

Meet Soni
Meet Soni

Reputation: 130

Dont use subquery for this, you can use simple update or else like this,

UPDATE actor AS s, (SELECT id  FROM actor WHERE fname='GROUCHO' AND lname = 'WILLIAMS') AS p
SET s.fname = 'HARPO' 
WHERE s.id = p.id;

DB FIDDLE LINK: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5ceb8e0d5d5837ce2ec13f18bfd103b2

Upvotes: 0

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

You don't need subquery for this; just match the first_name and last_name separately to get the row to update. Following query can also utilize the benefit of composite index (first_name, last_name) (if defined) on the actor table:

update actor
set actor.first_name = 'HARPO'
where actor.first_name = 'GROUCHO' 
       AND actor.last_name = 'WILLIAMS'

Upvotes: 4

Related Questions