MySQL Scripts

Convert Tables to Lowercase

SELECT 
    CONCAT('ALTER TABLE dbo.',
            table_name,
            ' RENAME TO dbo.',
            LOWER(table_name),
            ';')
FROM
    information_schema.tables
WHERE
    table_schema = 'dbo';

ALTER TABLE dbo.TableName RENAME TO dbo.tablename;

....

Drop a Table with Foreign Keys

SET foreign_key_checks = 0;
drop table DbName.TableName;
SET foreign_key_checks = 1;

Update a Table Column, Based on Results of Another Table

DROP TABLE IF EXISTS offices_query;
CREATE TEMPORARY TABLE offices_query
		SELECT DISTINCT ContentPages.Id, ContentPages.PageType
			FROM ContentPages
				WHERE ContentPages.PageType = 6
			ORDER BY ContentPages.Id;
            
UPDATE ContentPageVersions SET Indexed = 0 WHERE ContentPageId IN (SELECT Id FROM offices_query)
	AND (CultureId != 127);

Search from Multiple Tables

DROP TABLE IF EXISTS published_articles_query;
DROP TABLE IF EXISTS published_articles_query_2;
CREATE TEMPORARY TABLE published_articles_query   
	SELECT DISTINCT ContentPages.SubSiteId, ContentPages.Deleted, ContentPageVersions.ContentPageId, ContentPageVersions.Url,
			ContentPageVersions.PublishDate, ContentPageVersions.Id, ContentPageVersions.CultureId, ContentItems.SystemCodeId, ContentItems.Content,
			ContentItems.ContentPageVersionId
			FROM ContentPages, ContentPageVersions, SubSites, ContentItems
				WHERE (ContentPages.Id = ContentPageVersions.ContentPageId)
				AND (ContentPages.SubSiteId = 20 OR ContentPages.SubSiteId = 19)
				AND (ContentItems.ContentPageVersionId = ContentPageVersions.Id)
				AND ContentPages.Deleted = 0
				AND ContentPageVersions.PublishDate IS NOT null
				AND ContentItems.SystemCodeId = 8
			ORDER BY ContentPageId, PublishDate Asc;
        
CREATE TEMPORARY TABLE published_articles_query_2 SELECT * FROM published_articles_query;  
        
SELECT * 
	FROM published_articles_query
	WHERE id IN 
    (SELECT MIN(Id) FROM published_articles_query_2 GROUP BY ContentPageId, CultureId);

Join 3 Tables

Join 3 tables and avoid the duplicate column 'Id' problem.

DROP TABLE IF EXISTS emails_query;
CREATE TEMPORARY TABLE emails_query
	SELECT *
	FROM ContactForms2ContactEmails as cf2ce, ContactEmails as ce
	WHERE cf2ce.ContactEmailId = ce.Id;
    
SELECT * 
	FROM emails_query, ContactForms as cf
    WHERE emails_query.ContactFormId = cf.Id;