1

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
      ) ;

3 Answers 3

3

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'
0
1

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
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.