How To Select Rows in MySQL with No Matching Entry in Another Table

Author: , May 16th, 2018

SELECT t1.id FROM tableOne t1 LEFT JOIN tableTwo t2 ON t1.id = t2.id WHERE t2.id IS NULL The “WHERE t2.id IS NULL clause” restricts the results to only those rows where the id returned from tableTwo is null. tableTwo.id will be NULL for all records from tableOne where the id is not found in tableTwo.

How To Select the Max Numeric in a Varchar Column in MySQL

Author: , October 2nd, 2015

I needed to automate the addition of new users with a sequential member’s ID in WordPress. Additionally, there were non-numeric entries in that column that had to be ignored. The wp_users.user_login column is a varchar(60) and so does not naturally handle numeric operations well at all. The solution is a combination of REGEXP and cast(): […]

How To Enable the sa Login for MS SQL Server on AWS AMI

Author: , January 8th, 2013

To change security authentication mode: In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties. On the Security page, under Server authentication, select the new server authentication mode (Windows and SQL auth), and then click OK. In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to […]

How To Find Duplicate Records In MySQL

Author: , August 8th, 2012

SELECT * FROM yourTable GROUP BY yourField HAVING count(yourField) > 1;

How To Rename A MySQL Database

Author: , December 3rd, 2011

YMMV… 1 2 mysql –batch -u root -p -e "create database yourDataBaseBackup; SELECT CONCAT(‘RENAME TABLE ‘,table_schema,’.’,table_name,’ TO ‘,’yourDataBaseBackup.’,table_name,’;’) FROM information_schema.TABLES WHERE table_schema LIKE ‘yourDataBase’;" > backup.sql mysql -u root -p < backup.sql mysql –batch -u root -p -e “create database yourDataBaseBackup; SELECT CONCAT(‘RENAME TABLE ‘,table_schema,’.’,table_name,’ TO ‘,’yourDataBaseBackup.’,table_name,’;’) FROM information_schema.TABLES WHERE table_schema LIKE ‘yourDataBase’;” > backup.sql […]

How To Return Line Numbers In MySQL

Author: , October 14th, 2011

1 2 3 4 5 6 SELECT @rownum := @rownum + 1 AS Rank, T1.* FROM (select @rownum := 0) R, (    SELECT Category, Item, Value FROM Inventory    WHERE lastUpdated = CURDATE()    ORDER BY Value DESC ) AS T1 WHERE T1.Item = "Camera" SELECT @rownum := @rownum + 1 AS Rank, T1.* […]

How To Delete Rows In MySQL Using An Inner Join

Author: , October 14th, 2011

1 2 3 DELETE D FROM order_detail D INNER JOIN orders O ON D.orderid = O.id WHERE O.customerID = "$customerID" DELETE D FROM order_detail D INNER JOIN orders O ON D.orderid = O.id WHERE O.customerID = “$customerID”

How To Select Random Records Using MySQL

Author: , September 28th, 2011

1 2 3 4 5 SELECT * FROM yourTable WHERE yourField=’WhatYouWishToFindGoesHere’ ORDER BY RAND() LIMIT 5 SELECT * FROM yourTable WHERE yourField=’WhatYouWishToFindGoesHere’ ORDER BY RAND() LIMIT 5

How To Change A Password In WordPress Using The MySQL CLI

Author: , July 29th, 2011

1 2 UPDATE wp_users SET user_pass = MD5(’new_password_here’)    WHERE user_login = "login_to_change_here"; UPDATE wp_users SET user_pass = MD5(‘new_password_here’) WHERE user_login = “login_to_change_here”;

How To Copy Data From Table To Table Using MySQL

Author: , June 8th, 2011

1 2 CREATE TABLE newTable LIKE oldTable; INSERT INTO newTable SELECT * FROM oldTable; CREATE TABLE newTable LIKE oldTable; INSERT INTO newTable SELECT * FROM oldTable; 1 INSERT INTO newtable (`name`, `address1`, `address2`, `city`, `state`, `zip`, `phone`) SELECT `name`, `address1`, `address2`, `city`, `state`, `zip`, `phone` FROM oldtable; INSERT INTO newtable (`name`, `address1`, `address2`, `city`, `state`, […]