Slow variant: SELECT * FROM table t1 WHERE t1.domain_id = 1569 AND t1.is_active = 1 AND (t1.field IN (SELECT t2.field FROM table t2 WHERE t2.is_active = 1 GROUP BY t2.field HAVING COUNT(t2.field) > 1)) Faster variant: SELECT l0_.* FROM table l0_ inner join table n2 on n2.field = l0_.field where l0_.id n2.id AND l0_.is_active…
When trying to execute SELECT OR UPDATE query for rows different than a specific value, we should be aware to insert additional statement for null values too. For Example: UPDATE account_event SET account_event.assigned_to_id = 2 WHERE account_event.status != 1 With this query your goal is to update all rows where account_event.status is different from…
First deactivate foreign key check. Next execute the delete statement. Don’t forget to activate foreign key check again. Example: SET FOREIGN_KEY_CHECKS=0; DELETE FROM user WHERE id IN (55,62); SET FOREIGN_KEY_CHECKS=1;
A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table. * If you want to use foreign…
The promlem is to search in json column. Using MYSQL REGEXP is the solution: SELECT * FROM `orders` WHERE `json` REGEXP ‘(.*”op-number-29″:””.*)’ If we want to get all rows where op-number-29 is not equel to “”: SELECT * FROM `orders` WHERE `json` NOT REGEXP ‘(.*”op-number-29″:””.*)’
Simple example of generating new id’s in ascending order in MySQL. SET @count = 0; UPDATE `users` SET `users`.`id` = @count:= @count + 1;
It’s not so hard to update multiple rows with only one query. All we need to do is to use MySQL “CASE” functionality. mysql_query(” update category set price = case when name = 1 then ‘$_POST[price1]’ when name = 2 then ‘$_POST[price2]’ when name = 3 then ‘$_POST[price3]’ end WHERE name in (1,2,3)”);