CREATE TABLE `messages` ( `mes_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT COMMENT 'номер объявления', `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'дата/время дачи/изменения объявления', `expire` date NOT NULL COMMENT 'Дата истечения объявления', `paid_top_cat` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Наверху в разделе: 0 - нет, 1 - запрошен, 2 - включен', `paid_top_main` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Наверху на главной: 0 - нет, 1 - запрошен, 2 - включен', `paid_links` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Использование ссылок: 0 - нет, 1 - запрошен, 2 - включен', `paid_border` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Использование рамки: 0 - нет, 1 - запрошен, 2 - включен', `expire_top_cat` date DEFAULT NULL, `expire_top_main` date DEFAULT NULL, `expire_links` date DEFAULT NULL, `expire_border` date DEFAULT NULL, PRIMARY KEY (`mes_id`), # more fields ) ENGINE=MyISAM ;
I need to update some fields of a table dependently on some fields of the same table. Is the following in an UPDATE a good idea? paid_top_cat=IF(paid_top_cat=2 AND expire_to_cat<NOW(), 1, paid_top_cat), expire_top_cat=IF(expire_top_cat<NOW(), NULL, expire_top_cat)
and so on for other expire_* and paid_* fields in a single statement.
What are arguments pro and counter to use this tricky UPDATE statements with many IF functions vs updating every column (or rather a pair of paid_* and expire_* columns) at once, so running UPDATE max 4 times?
MySQL.
Best Answer
When update executed once it's guaranteed that all rows that satisfy conditions will be updated (or none in case of error). 4 separate updates may update different rows (something may change between updates, and you're using MyIsam, so transactions won't help; in addition, your
update
depends on non-deterministicNOW()
).Another point is that each
UPDATE
locks set of rows , so 4 updates will lock rows 4 times even if the same rows are affected(in case of MyISAM)