/ Gists / MySql

Gists - MySql

On gists

Mysql update join

MySql

update-join.sql #

UPDATE table A
JOIN table B ON {join fields}
JOIN table C ON {join fields}
JOIN {as many tables as you need}
SET A.column = {expression}
Example:

UPDATE person P
JOIN address A ON P.home_address_id = A.id
JOIN city C ON A.city_id = C.id
SET P.home_zip = C.zipcode;
On gists

Prunik datumů - hranice

MySql

prunik-hranice.sql #

select
    *
from
    rezervation_repeatedly 
where
    date_from <= '$week_end'     
    and date_to >= '$week_start'
order by
    id asc
On gists

Mysql variables run like query

MySql

mysql-query-variable.sql #

SET @table = 'article_lang';

SET @q1 = CONCAT('UPDATE ', @table,' SET language_id = 1000 WHERE language_id = 1');
SET @q2 = CONCAT('UPDATE ', @table,' SET language_id = 1 WHERE language_id = 2');
SET @q3 = CONCAT('UPDATE ', @table,' SET language_id = 2 WHERE language_id = 1000');

PREPARE stmt FROM @q1;
EXECUTE stmt;

PREPARE stmt FROM @q2;
EXECUTE stmt;

PREPARE stmt FROM @q3;
EXECUTE stmt;
On gists

From http://blog.bobbyallen.me/2015/04/04/get-nearest-places-from-mysql-with-latitude-and-longditude/

MySql

lat-lng.sql #

-- Set your users current location in LAT/LONG here
set @lat=51.891648;
set @lng=0.244799;

-- The main SQL query that returns the closest 5 airports.
SELECT id, icao, lat, lng, 111.045 * DEGREES(ACOS(COS(RADIANS(@lat))
 * COS(RADIANS(lat))
 * COS(RADIANS(lng) - RADIANS(@lng))
 + SIN(RADIANS(@lat))
 * SIN(RADIANS(lat))))
 AS distance_in_km
FROM airports
ORDER BY distance_in_km ASC
LIMIT 0,5;
On gists

Delete from table with limit (first X rows excluded)

•
MySql

delete from table with limit.sql #

DELETE FROM table
WHERE ID IN
        (
        SELECT ID
        FROM
            (
                SELECT ID
                FROM table
                WHERE Type = 'TEST'
                ORDER BY ID
                LIMIT 30,60
            ) a
        )
On gists

Delete from table with alias

•
MySql

Delete from table with alias.sql #

DELETE `cl`
FROM `ContentLink` `cl`
WHERE `cl`.`lorder` = 34;
On gists

Convert tables to InnoDB

•
MySql

convert-to-innodb #

SET @DATABASE_NAME = 'test';

SELECT  CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DATABASE_NAME
AND     `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;