echo -n "Lingerie Sets" | tr "A-Z" "a-z" | tr -cs "a-z" "-"
lingerie-sets

SELECT *
FROM   `CATEGORIES`
WHERE  `NAME` REGEXP REPLACE('lingerie-sets','-','.+')

DROP FUNCTION `sf_uri_normalise`//
CREATE DEFINER=`root`@`localhost` FUNCTION `sf_uri_normalise`(s VARCHAR(100))
    RETURNS varchar(100) CHARSET utf8
    NO SQL
    DETERMINISTIC
    COMMENT 'normalise a string to a SEO-friendly uri'
BEGIN
        RETURN LOWER(REPLACE(s, ' ', '-'));
END

MrMoo fucked around with this message at 20:05 on Jan 30, 2009

MariaDB [trth]> explain SELECT max(bid_price) AS bid_price, exchange FROM quotes WHERE quotes.ric = 'A' AND timestamp 
BETWEEN @start_time AND @end_time GROUP BY exchange ORDER BY NULL;
+------+-------------+--------+-------+---------------+---------------+---------+------+------+------------------------------+
| id   | select_type | table  | type  | possible_keys | key           | key_len | ref  | rows | Extra                        |
+------+-------------+--------+-------+---------------+---------------+---------+------+------+------------------------------+
|    1 | SIMPLE      | quotes | range | ric_timestamp | ric_timestamp | 9       | NULL |  659 | Using where; Using temporary |
+------+-------------+--------+-------+---------------+---------------+---------+------+------+------------------------------+
1 row in set (0.00 sec)
  
MariaDB [trth]> set @trade_ric ='A';
Query OK, 0 rows affected (0.00 sec)

MariaDB [trth]> explain SELECT max(bid_price) AS bid_price, exchange FROM quotes WHERE quotes.ric = @trade_ric AND timestamp
 BETWEEN @start_time AND @end_time GROUP BY exchange ORDER BY NULL;
+------+-------------+--------+------+---------------+------+---------+------+----------+------------------------------+
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows     | Extra                        |
+------+-------------+--------+------+---------------+------+---------+------+----------+------------------------------+
|    1 | SIMPLE      | quotes | ALL  | NULL          | NULL | NULL    | NULL | 10841597 | Using where; Using temporary |
+------+-------------+--------+------+---------------+------+---------+------+----------+------------------------------+
1 row in set (0.00 sec)

MrMoo fucked around with this message at 20:03 on Dec 4, 2014

MrMoo fucked around with this message at 23:18 on Dec 5, 2014

article_id |  code
-----------+-------
  100      |  one
  100      |  two
  200      |  one
  300      |  three

MrMoo fucked around with this message at 13:58 on Jul 22, 2015

UPDATE assets SET is_deleted WHERE id = ?;

UPDATE assets SET is_deleted WHERE id = ? AND is_deleted = 0;

UPDATE assets SET is_deleted WHERE id = ? AND is_deleted = 0;
CASE WHEN (changes() = 0) THEN
  SELECT COUNT(*) AS is_exists, is_deleted FROM assets WHERE id = ?
ELSE
  SELECT 1 AS is_exists, 1 AS is_deleted
END;

MrMoo fucked around with this message at 04:11 on Oct 7, 2023

UPDATE demo AS x
SET name = "frog"
FROM demo AS y
WHERE x.id = y.id
    AND x.id = 3
RETURNING y.name AS old_name;

• If id matches and is not already soft-deleted. This is detectable via changes() = 1 if there is the additional WHERE is_deleted = 0 clause.
  
• If id matches and is already soft-deleted. The basic logic would be that changes() = 0 then verify that is_deleted = 1. Otherwise the above PGSQL syntax of detecting the value of is_deleted before the UPDATE would achieve the same goal.
  
• If id does not match any rows, i.e. changes() = 0 and a SELECT COUNT(*) FROM assets WHERE id = 1 would be zero.
  

BEGIN TRANSACTION;
SELECT name FROM demo WHERE id = 3;
UPDATE demo SET name = "frog" WHERE id = 3 AND name <> "frog";
COMMIT;

UPDATE demo
SET name = 'frog'
WHERE id = 3 AND name <> 'frog'
RETURNING name;

MrMoo fucked around with this message at 18:28 on Oct 7, 2023