/ Gists / MySql - advanced

Gists - MySql - advanced

On gists

Update table col by random date / string

MySql MySql tricks MySql - advanced

update.sql #

-- datetime
SET @MIN = '2019-06-29 00:53:27';
SET @MAX = '2019-06-29 13:53:27';

UPDATE tablename
SET columnname = TIMESTAMPADD(SECOND, FLOOR(RAND() * TIMESTAMPDIFF(SECOND, @MIN, @MAX)), @MIN)

-- https://stackoverflow.com/questions/24378490/update-table-with-random-values-from-given-set-of-string-values
-- string
update table t
    set col = elt(floor(rand()*3) + 1, 'value1', 'value2', 'value3');
    
-- or
UPDATE `table`
SET `column`=(CASE CEIL(RAND()*3)
              WHEN 1 THEN 'value1'
              WHEN 2 THEN 'value2'
              WHEN 3 THEN 'value3'
          END);
          
          
-- random numbet between N a M

-- For range (min..max( (min inclusive, max exclusive) it is:
FLOOR( RAND() * (max-min) + min )
 
-- For range (min..max) (min+max inclusive) it is:
FLOOR( RAND() * (max-min+1) + min )
On gists

Stored Procedures - LS

MySql MySql - advanced

sp1.sql #


set character set utf8;

-- deprecated (deleted)
DROP PROCEDURE IF EXISTS odds_old_1x2;
DROP PROCEDURE IF EXISTS odds_old_moneyline;
DROP PROCEDURE IF EXISTS odds_old_underover;
DROP PROCEDURE IF EXISTS odds_old_handicap;
DROP PROCEDURE IF EXISTS split_string1;
DROP PROCEDURE IF EXISTS split_string2;

--correct_url (inc/functions.inc.php)
DELIMITER |
DROP PROCEDURE IF EXISTS correct_url;
CREATE PROCEDURE correct_url(game_id INT, lang_id INT)
MODIFIES SQL DATA
BEGIN
	DROP TABLE IF EXISTS BE_TMP_Game;
	CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_Game ENGINE=MEMORY
	SELECT m.URL AS aLeagueURL, s.URL AS aSportURL, c.URL AS aCountryURL
	FROM
		BE_Game AS g,
		BE_Match AS m,
		BE_Sport AS s,
		BE_League AS l,
		BE_Country AS c
	WHERE
		g.GameID = game_id
		AND g.MatchID = m.MatchID AND m.LangID = lang_id
		AND m.SportID = s.SportID AND s.LangID = lang_id
		AND m.LeagueID = l.LeagueID
		AND l.CountryID = c.CountryID AND c.LangID = lang_id;

	IF((SELECT COUNT(*) FROM BE_TMP_Game) = 0) THEN
		CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_Game ENGINE=MEMORY
		SELECT m.URL AS aLeagueURL, s.URL AS aSportURL, c.URL AS aCountryURL
		FROM
			BE_GameArchive AS g,
			BE_Match AS m,
			BE_Sport AS s,
			BE_League AS l,
			BE_Country AS c
		WHERE
			g.GameID = game_id
			AND g.MatchID = m.MatchID AND m.LangID = lang_id
			AND m.SportID = s.SportID AND s.LangID = lang_id
			AND m.LeagueID = l.LeagueID
			AND l.CountryID = c.CountryID AND c.LangID = lang_id;
	END IF;

	SELECT * FROM BE_TMP_Game;

	DROP TABLE BE_TMP_Game;
END|
DELIMITER ;

--get_mutual_team (mutual)
DELIMITER |
DROP PROCEDURE IF EXISTS get_mutual_team;
CREATE PROCEDURE get_mutual_team(league_id INT, lang_id INT)
MODIFIES SQL DATA
BEGIN
	DROP TABLE IF EXISTS BE_TMP_Game;

	CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_Game ENGINE=MEMORY
	SELECT tg1.TeamID, tg1.Name
	FROM
		BE_Game AS g,
		BE_TeamGroup AS tg1 
	WHERE
		g.MatchID = league_id
		AND g.HomeID = tg1.TeamID AND tg1.LangID = lang_id
	GROUP BY tg1.Name;

	IF((SELECT COUNT(*) FROM BE_TMP_Game) = 0) THEN
		CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_Game ENGINE=MEMORY
		SELECT tg1.TeamID, tg1.Name
		FROM
			BE_GameArchive AS g,
			BE_TeamGroup AS tg1 
		WHERE
			g.MatchID = league_id
			AND g.HomeID = tg1.TeamID AND tg1.LangID = lang_id
		GROUP BY tg1.Name;
	END IF;

	SELECT * FROM BE_TMP_Game ORDER BY Name;

	DROP TABLE BE_TMP_Game;
END|
DELIMITER ;

--odds_1x2 (nextmatch)
DELIMITER |
DROP PROCEDURE IF EXISTS odds_1x2;
CREATE PROCEDURE odds_1x2(league_id INT, lang_id INT, project_id INT, timezone CHAR(6))
MODIFIES SQL DATA
BEGIN
	DROP TABLE IF EXISTS BE_TMP_Odds;

	SET @ldb = (SELECT DBPostfix FROM BE_Project WHERE idProject = project_id LIMIT 1);

	SET @query = CONCAT("CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_Odds ENGINE=MEMORY
							SELECT
								g.idCourse, o.OfficeID, o.Name AS aBettingOffice, d.Description, o.OriginID, u.URL,
								g.BettingOfficeId, g.K1, g.K2, g.K0, g.K10, g.K02,
								DATE_FORMAT(CONVERT_TZ(g.Created, '+1:00', '", timezone, "'), '%d/%m %H:%i')  AS aCreated,
								IF(og.K1 IS NULL OR og.K1 = 0, g.K1, og.K1) AS aOldK1, IF(og.K2 IS NULL OR og.K2 = 0, g.K2, og.K2) AS aOldK2, IF(og.K0 IS NULL OR og.K0 = 0, g.K0, og.K0) AS aOldK0,
								IF(og.K10 IS NULL OR og.K10 = 0, g.K10, og.K10) AS aOldK10, IF(og.K02 IS NULL OR og.K02 = 0, g.K02, og.K02) AS aOldK02,
								DATE_FORMAT(IF(og.Created IS NULL OR og.Created = 0, CONVERT_TZ(g.Created, '+1:00', '", timezone, "'), CONVERT_TZ(og.Created, '+1:00', '", timezone, "')), '%d/%m %H:%i')  AS aOldCreated
							FROM
								BE_Course AS g
									LEFT JOIN BE_CourseOld AS og ON (g.idCourse = og.CourseID),
								BE_Office AS o
									LEFT JOIN BE_OfficeDescription AS d ON (o.idOffice = d.OfficeID AND d.LangID = ", lang_id, ")
									LEFT JOIN betexplorer_", @ldb, ".BettingOfficeURL AS u ON (u.OfficeID = o.OfficeID)
							WHERE
								g.GameID = ", league_id, "
								AND g.BettingOfficeID = o.OfficeID
							GROUP BY
								g.idCourse
							ORDER BY
								og.idCourse");

	PREPARE course FROM @query;
	EXECUTE course;
	DEALLOCATE PREPARE course;

--	DROP TABLE IF EXISTS BE_TMP_OddsOld;

--	CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_OddsOld ENGINE=MEMORY
--	SELECT o.idCourse, o.CourseID, o.K1, o.K0, o.K2, o.Created, DATE_FORMAT(CONVERT_TZ(o.Created, '+1:00', '", timezone, "'), '%d/%m %H:%i') AS aCreated
--	FROM
--		BE_TMP_Odds AS tmp,
--		BE_CourseOld AS o
--	WHERE
--		tmp.idCourse = o.CourseID;
END|
DELIMITER ;

--odds_underover (nextmatch)
DELIMITER |
DROP PROCEDURE IF EXISTS odds_underover;
CREATE PROCEDURE odds_underover(league_id INT, lang_id INT, project_id INT, timezone CHAR(6))
MODIFIES SQL DATA
BEGIN
	DROP TABLE IF EXISTS BE_TMP_Odds;

	SET @ldb = (SELECT DBPostfix FROM BE_Project WHERE idProject = project_id LIMIT 1);

	SET @query = CONCAT("CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_Odds ENGINE=MEMORY
							SELECT
								g.idCourse, g.`Value`, o.OfficeID, o.Name AS aBettingOffice, d.Description, o.OriginID, u.URL,
								g.BettingOfficeId, g.K1, g.K2,
								DATE_FORMAT(CONVERT_TZ(g.Created, '+1:00', '", timezone, "'), '%d/%m %H:%i')  AS aCreated,
								IF(og.K1 IS NULL OR og.K1 = 0, g.K1, og.K1) AS aOldK1, IF(og.K2 IS NULL OR og.K2, g.K2, og.K2) AS aOldK2,
								DATE_FORMAT(IF(og.Created IS NULL OR og.Created = 0, CONVERT_TZ(g.Created, '+1:00', '", timezone, "'), CONVERT_TZ(og.Created, '+1:00', '", timezone, "')), '%d/%m %H:%i')  AS aOldCreated
							FROM
								BE_Course2 AS g
									LEFT JOIN BE_Course2Old AS og ON (g.idCourse = og.CourseID),
								BE_Office AS o
									LEFT JOIN BE_OfficeDescription AS d ON (o.idOffice = d.OfficeID AND d.LangID = ", lang_id, ")
									LEFT JOIN betexplorer_", @ldb, ".BettingOfficeURL AS u ON (u.OfficeID = o.OfficeID)
							WHERE
								g.GameID = ", league_id, "
								AND g.BettingOfficeID = o.OfficeID
							GROUP BY
								g.idCourse
							ORDER BY
								og.idCourse;");

	PREPARE course FROM @query;
	EXECUTE course;
	DEALLOCATE PREPARE course;

	DROP TABLE IF EXISTS BE_TMP_OddsOld;

	CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_OddsOld ENGINE=MEMORY
	SELECT o.idCourse, o.CourseID, o.K1, o.K2, o.Created, DATE_FORMAT(CONVERT_TZ(o.Created, '+1:00', '", timezone, "'), '%d/%m %H:%i') AS aCreated
	FROM
		BE_TMP_Odds AS tmp,
		BE_Course2Old AS o
	WHERE
		tmp.idCourse = o.CourseID;

END|
DELIMITER ;

--odds_handicap (nextmatch)
DELIMITER |
DROP PROCEDURE IF EXISTS odds_handicap;
CREATE PROCEDURE odds_handicap(league_id INT, lang_id INT, project_id INT, timezone CHAR(6))
MODIFIES SQL DATA
BEGIN
	DROP TABLE IF EXISTS BE_TMP_Odds;

	SET @ldb = (SELECT DBPostfix FROM BE_Project WHERE idProject = project_id LIMIT 1);

	SET @query = CONCAT("CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_Odds ENGINE=MEMORY
							SELECT
								g.idCourse, g.`Value`, o.OfficeID, o.Name AS aBettingOffice, d.Description, o.OriginID, u.URL,
								g.BettingOfficeId, g.K1, g.K2,
								DATE_FORMAT(CONVERT_TZ(g.Created, '+1:00', '", timezone, "'), '%d/%m %H:%i')  AS aCreated,
								IF(og.K1 IS NULL OR og.K1 = 0, g.K1, og.K1) AS aOldK1, IF(og.K2 IS NULL OR og.K2 = 0, g.K2, og.K2) AS aOldK2,
								DATE_FORMAT(IF(og.Created IS NULL OR og.Created = 0, CONVERT_TZ(g.Created, '+1:00', '", timezone, "'), CONVERT_TZ(og.Created, '+1:00', '", timezone, "')), '%d/%m %H:%i')  AS aOldCreated
							FROM
								BE_Course3 AS g
									LEFT JOIN BE_Course3Old AS og ON (g.idCourse = og.CourseID),
								BE_Office AS o
									LEFT JOIN BE_OfficeDescription AS d ON (o.idOffice = d.OfficeID AND d.LangID = ", lang_id, ")
									LEFT JOIN betexplorer_", @ldb, ".BettingOfficeURL AS u ON (u.OfficeID = o.OfficeID)
							WHERE
								g.GameID = ", league_id, "
								AND g.BettingOfficeID = o.OfficeID
							GROUP BY
								g.idCourse
							ORDER BY
								og.idCourse;");

	PREPARE course FROM @query;
	EXECUTE course;
	DEALLOCATE PREPARE course;

	DROP TABLE IF EXISTS BE_TMP_OddsOld;

	CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_OddsOld ENGINE=MEMORY
	SELECT o.idCourse, o.CourseID, o.K1, o.K2, o.Created, DATE_FORMAT(CONVERT_TZ(o.Created, '+1:00', '", timezone, "'), '%d/%m %H:%i') AS aCreated
	FROM
		BE_TMP_Odds AS tmp,
		BE_Course3Old AS o
	WHERE
		tmp.idCourse = o.CourseID;

END|
DELIMITER ;

--odds_moneyline (nextmatch)
DELIMITER |
DROP PROCEDURE IF EXISTS odds_moneyline;
CREATE PROCEDURE odds_moneyline(league_id INT, lang_id INT, project_id INT, timezone CHAR(6))
MODIFIES SQL DATA
BEGIN
	DROP TABLE IF EXISTS BE_TMP_Odds;

	SET @ldb = (SELECT DBPostfix FROM BE_Project WHERE idProject = project_id LIMIT 1);

	SET @query = CONCAT("CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_Odds ENGINE=MEMORY
							SELECT
								g.idCourse, o.OfficeID, o.Name AS aBettingOffice, d.Description, o.OriginID, u.URL,
								g.BettingOfficeId, g.K1, g.K2,
								DATE_FORMAT(CONVERT_TZ(g.Created, '+1:00', '", timezone, "'), '%d/%m %H:%i')  AS aCreated,
								IF(og.K1 IS NULL OR og.K1 = 0, g.K1, og.K1) AS aOldK1, IF(og.K2 IS NULL OR og.K2 = 0, g.K2, og.K2) AS aOldK2,
								DATE_FORMAT(IF(og.Created IS NULL OR og.Created = 0, CONVERT_TZ(g.Created, '+1:00', '", timezone, "'), CONVERT_TZ(og.Created, '+1:00', '", timezone, "')), '%d/%m %H:%i')  AS aOldCreated
							FROM
								BE_Course4 AS g
									LEFT JOIN BE_Course4Old AS og ON (g.idCourse = og.CourseID),
								BE_Office AS o
									LEFT JOIN BE_OfficeDescription AS d ON (o.idOffice = d.OfficeID AND d.LangID = ", lang_id, ")
									LEFT JOIN betexplorer_", @ldb, ".BettingOfficeURL AS u ON (u.OfficeID = o.OfficeID)
							WHERE
								g.GameID = ", league_id, "
								AND g.BettingOfficeID = o.OfficeID
							GROUP BY
								g.idCourse
							ORDER BY
								og.idCourse;");

	PREPARE course FROM @query;
	EXECUTE course;
	DEALLOCATE PREPARE course;

	DROP TABLE IF EXISTS BE_TMP_OddsOld;

	CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_OddsOld ENGINE=MEMORY
	SELECT o.idCourse, o.CourseID, o.K1, o.K2, o.Created, DATE_FORMAT(CONVERT_TZ(o.Created, '+1:00', '", timezone, "'), '%d/%m %H:%i') AS aCreated
	FROM
		BE_TMP_Odds AS tmp,
		BE_Course4Old AS o
	WHERE
		tmp.idCourse = o.CourseID;

END|
DELIMITER ;

--teaminfo1 (teaminfo.php)
DELIMITER |
DROP PROCEDURE IF EXISTS teaminfo1;
CREATE PROCEDURE teaminfo1(league_id INT, team_id INT, lang_id INT, timezone CHAR(6))
MODIFIES SQL DATA
BEGIN
	DROP TABLE IF EXISTS BE_TMP_Game;

	CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_Game ENGINE=MEMORY
	SELECT
		g.GameID, DATE_FORMAT(CONVERT_TZ(g.`Date`, '+1:00', timezone), '%d.%m.%Y') AS aDate, g.`Date`, g.Round, g.HomeID, g.GuestID,
		tg1.Name AS aHomeName, tg2.Name AS aGuestName, tg1.URL AS aHomeURL, tg2.URL AS aGuestURL
	FROM
		BE_Game AS g,
		BE_Match AS m,
		BE_TeamGroup AS tg1,
		BE_TeamGroup AS tg2
	WHERE
		g.MatchID = league_id
		AND g.Played = 'n'
		AND g.MatchID = m.MatchID AND m.LangID = lang_id
		AND (g.HomeID = team_id OR g.GuestID = team_id)
		AND g.HomeID = tg1.TeamID AND tg1.LangID = lang_id
		AND g.GuestID = tg2.TeamID AND tg2.LangID = lang_id;

	CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_Game ENGINE=MEMORY
	SELECT
		g.GameID, DATE_FORMAT(CONVERT_TZ(g.`Date`, '+1:00', timezone), '%d.%m.%Y') AS aDate, g.`Date`, g.Round, g.HomeID, g.GuestID,
		tg1.Name AS aHomeName, tg2.Name AS aGuestName, tg1.URL AS aHomeURL, tg2.URL AS aGuestURL
	FROM
		BE_GameArchive AS g,
		BE_Match AS m,
		BE_TeamGroup AS tg1,
		BE_TeamGroup AS tg2
	WHERE
		g.MatchID = league_id
		AND g.Played = 'n'
		AND g.MatchID = m.MatchID AND m.LangID = lang_id
		AND (g.HomeID = team_id OR g.GuestID = team_id)
		AND g.HomeID = tg1.TeamID AND tg1.LangID = lang_id
		AND g.GuestID = tg2.TeamID AND tg2.LangID = lang_id;

	SELECT * FROM BE_TMP_Game ORDER BY `Date` DESC;
	DROP TABLE IF EXISTS BE_TMP_Game;
END|
DELIMITER ;
On gists

Swap two columns values

MySql MySql tricks MySql - advanced

swap.sql #

UPDATE swap_test s1, swap_test s2 SET s1.x=s1.y, s1.y=s2.x WHERE s1.id=s2.id;

-- tipliga
UPDATE
 _tipliga_tipy_hracu t1, 
 _tipliga_tipy_hracu t2  
SET t1.g1=t1.g2, t1.g2=t2.g1 WHERE t1.id=t2.id
AND (t1.id_zapasu = 1462)
On gists

Novato - ranks

MySql tricks MySql - advanced AW

rank-model.php #


    public function triggerEvent(array $events = [], Nette\Database\Table\ActiveRow $section = null, $debug = false)
    {
        if($debug)
        {
            $logFile = INDEX_DIR . '/trigger-event.log';
            $logData = [];
            foreach ($events as $event)
            {
                $logData[] = sprintf('%s | %s | sectionId: %d', 
                                        date('Y-m-d H:i:s'), 
                                        $event, 
                                        ($section !== null) ? $section->id : $section
                                    ) 
                                    . PHP_EOL;
            }
            
            file_put_contents('nette.safe://' . $logFile, $logData, FILE_APPEND);
        }

        $sectionIndependentEvents = ['onAny'];

        $whereOrParams = $whereOr = [];
        foreach($events as $event)
        {
            $whereOr[] = 'FIND_IN_SET(?, event)';
            $whereOrParams[] = $event;
        }

        $presenters = $this->connection->table('catalogue_presenter_config')
            ->where(implode(' OR ', $whereOr), ...$whereOrParams);
        
        $pagesToInvalidate = [];                                       
        foreach ($presenters as $presenterRow)
        {
            $pages = $this->connection
                ->table('catalogue_page')
                ->where('catalogue_section.catalogue_id', $section->catalogue_id)
                ->where('presenter_id', $presenterRow->presenter_id);

            $invalidateIndependent = FALSE;
            foreach(explode(',', $presenterRow->event) as $event)
                if(in_array($event, $sectionIndependentEvents))
                    $invalidateIndependent = TRUE;

            if(!$invalidateIndependent)
                $page->where('catalogue_section_id', $section->id);
            
            foreach ($pages as $page)
                $pagesToInvalidate[$page->id] = $page;
        }   
        
        foreach ($pagesToInvalidate as $pageId => $page)
            $this->invalidPdf($page);
    }

    public function recountPageNumbersInCatalogue($catalogueId)
    {
        $this->connection->query('
            UPDATE 
            catalogue_page 
            JOIN (
                SELECT
                tmp2.*,
                @totalRank := @totalRank + tmp2.prev as lastPageNumberInCatalogue
                FROM 
                (SELECT @totalRank := 0) as tempTotalRank,
                (
                    SELECT
                    tmp.*,
                    @prev as prev,
                    @prev := tmp.pdf_pages_count
                    
                    FROM
                    (
                        SELECT 
                        @prev := 0,
                        t2.name AS sectionName,
                        t2.rank AS sectionRank,
                        t1.rank AS pageRank,
                        t1.id,
                        t1.pdf_pages_count
                        
                        FROM 
                    
                        catalogue_page t1
                        JOIN catalogue_section t2 ON t2.id = t1.catalogue_section_id
                        WHERE t2.catalogue_id = ?
                        ORDER BY sectionRank ASC, pageRank ASC
                    ) AS tmp
                    
                ) AS tmp2

            ) tmpJoin ON tmpJoin.id = catalogue_page.id
            SET page_number = lastPageNumberInCatalogue + 1
        ', $catalogueId);
    }
On gists

SQL select only rows with max value on a column (2 ways) - universal

MySql MySql tricks MySql - advanced

solution.sql #

-- @link: https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1

-- Joining with simple group-identifier, max-value-in-group Sub-query
SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev


-- Left Joining with self, tweaking join conditions and filters
SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;
On gists

Dynamic SQL via prepared statements

•
MySql MySql tricks MySql - advanced

example.sql #

-- 1
SET @table = "pexeso";
SET @sql:= CONCAT('SELECT * FROM',' ', @table);
PREPARE stmt FROM @sql;
EXECUTE stmt;
-- DEALLOCATE PREPARE stmt;  # neni povinne


-- 1b, lze dosadit parametry pres ?
SET @id = 1;
PREPARE stmt FROM 'SELECT * FROM category WHERE id = ?';
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt; 




-- 2, Dynamically Building SQL based on Parameters
CREATE DEFINER=`root`@`localhost` 
  PROCEDURE `GetRecentActivity`(input VARCHAR(15))
BEGIN
SET @input = input;

if @input="asc" then
    SET @sort = " order by activity_log_key asc";
elseif @input = "desc" then
    SET @sort = " order by activity_log_key desc";
else
    SET @sort ="";
end if;

SET @query = CONCAT('select * from activitylog ',@sort,' limit 0, 5');

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END


-- 3, more than one column
SELECT GROUP_CONCAT(COLUMN_NAME) FROM information_schema.`COLUMNS` C 
WHERE table_name = 'MyTb' AND COLUMN_NAME LIKE '%whatever%' INTO @COLUMNS;

SET @table = 'MyTb';
SET @s = CONCAT('SELECT ',@columns,' FROM ', @table);

PREPARE stmt FROM @s;
EXECUTE stmt;
On gists

Triggers

•
MySql MySql tricks MySql - advanced

clanky.sql #

CREATE TRIGGER diskuse_ai AFTER INSERT ON diskuse FOR EACH ROW UPDATE clanky SET diskuse_pocet = diskuse_pocet + 1 WHERE id = NEW.clanek;
CREATE TRIGGER diskuse_ad AFTER DELETE ON diskuse FOR EACH ROW UPDATE clanky SET diskuse_pocet = diskuse_pocet - 1 WHERE id = OLD.clanek;
CREATE TRIGGER diskuse_au AFTER UPDATE ON diskuse FOR EACH ROW UPDATE clanky SET diskuse_pocet = diskuse_pocet + IF(id = NEW.clanek, 1, -1) WHERE id = OLD.clanek XOR id = NEW.clanek;
On gists

Maximum value by each row

•
Popular ⭐ MySql MySql - advanced Nette-Tricks

structure.sql #


CREATE TABLE shop (
    article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
    dealer  CHAR(20)                 DEFAULT ''     NOT NULL,
    price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL,
    PRIMARY KEY(article, dealer));
INSERT INTO shop VALUES
    (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
    (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);
    
    

    
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  3.45 |
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | B      |  1.45 |
|    0003 | C      |  1.69 |
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+
On gists

Procedury - MIX

•
MySql MySql - advanced

poradi-tymu.sql #

CREATE PROCEDURE SP_poradi()
BEGIN
 
DECLARE s_poradi INT(5);
DECLARE s_body INT(5);
DECLARE temp_body INT(5) DEFAULT 0;
DECLARE s_tym VARCHAR(50);
DECLARE my_rank INT(5) DEFAULT 0;
DECLARE done TINYINT(1) DEFAULT 0;
 
DECLARE i INT;
 
DECLARE rank CURSOR FOR
SELECT tym, body, poradi FROM poradi
ORDER BY body DESC;
 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
 
SET i = 0;
OPEN rank;
rank_loop:  LOOP
 
FETCH rank INTO s_tym, s_body, s_poradi;
IF done THEN LEAVE rank_loop; END IF;

 
IF (temp_body <> s_body)
  THEN UPDATE poradi SET poradi = i + 1 WHERE tym = s_tym;
ELSE
  UPDATE poradi SET poradi = i + 0 WHERE tym = s_tym;
END IF;
 
SET i=i+1;
SET temp_body = s_body;  
 
 
END LOOP rank_loop;
CLOSE rank;
 
END;