Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
      ) ;
share|improve this question

migrated from stackoverflow.com Sep 9 '12 at 9:18

This question came from our site for professional and enthusiast programmers.

add comment

3 Answers 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'
share|improve this answer
    
thanks will check it out.. –  Abhishek Sep 9 '12 at 14:33
add comment

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
share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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