query optimization for update nested query IN(SELECT

Question

Can we optimize this?

UPDATE TABLE_NOTE 
SET COL_NOTE_DISPLAY='BLOCK' 
WHERE COL_NOTE_BIBID = $projectID 
  AND $COL_NOTE_ID IN
      ( SELECT COL_PILENOTEREL_NOTE_ID 
        FROM TABLE_PILENOTEREL 
        WHERE $COL_PILENOTEREL_PILE_ID = $pileId
      ) ;

Answer 1

How about:

UPDATE 
(select * from TABLE_NOTE
where COL_NOTE_BIBID = $projectID
join TABLE_PINENOTEREL on $COL_PILENOTEREL_NOTE_ID = $COL_NOTE_ID
WHERE $COL_PILENOTEREL_PILE_ID = $pileId)
SET COL_NOTE_DISPLAY='BLOCK'

Answer 2

Just use a join in the tables list. MySQL 5 allows this in updates.

UPDATE
  TABLE_NOTE
  INNER JOIN TABLE_PILENOTEREL
    ON TABLE_PILENOTEREL.COL_PILENOTEREL_NOTE_ID = $COL_NOTE_ID
SET
  TABLE_NOTE.COL_NOTE_DISPLAY = 'BLOCK'
WHERE
  TABLE_NOTE.COL_NOTE_BIBID = $projectID
  AND TABLE_PILENOTEREL.$COL_PILENOTEREL_PILE_ID = $pileId

Answer 3

I think a better one would be

UPDATE TABLE_NOTE TN JOIN TABLE_PILENOTEREL TP ON 
TN.COL_NOTE_ID = TP.COL_PILENOTEREL_NOTE_ID 
SET TN.COL_NOTE_DISPLAY='BLOCK' 
WHERE TN.COL_NOTE_BIBID = $projectID AND TP.COL_PILENOTEREL_PILE_ID = $pileId;

It dont requires IN or sub queries(1 select is saved).You can perform it from a join only.

So you have advantage of indexes if you have it on the ON and WHERE column of join.