DUPLICATE KEY can be used to DUPLICATE to Insert into a table or update if exists in MySQL. INSERT INTO tableName (id, name, age) VALUES(10, “Roy”, 34) ON DUPLICATE KEY UPDATE name=”Roy”, age=34 It will insert a new record if no record exists with id = 10, Otherwise update if any record exists with id … Read more
Select the First Row In Each Group of MySQL Here is an example: SELECT MIN(purchases2.id), # change to MAX if you want the highest value purchases1.customer, purchases1.total FROM PURCHASES purchases1 JOIN (SELECT p.customer, MAX(total) AS max_total FROM PURCHASES purchases GROUP BY p.customer) purchases2 ON purchases2.customer = purchases1.customer AND purchases2.max_total = purchases1.total GROUP BY purchases1.customer, purchases1.total
Alter Table if Column Not Exist in MySQL To alter the table if the column does not exist: IF NOT EXISTS( SELECT NULL FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ‘tableName’ AND table_schema = ‘database_name’ AND column_name = ‘columnName’) THEN ALTER TABLE `TableName` ADD `ColumnName` int(1) NOT NULL default ‘0’; END IF;
Update From Another Table MySQL We can update another table with the help of the inner join. UPDATE TableName1 SET TableName1.col1 = TableName2.col1, TableName1.col2 = TableName2.col2 FROM Some_Table AS TableName1 INNER JOIN Other_Table AS TableName2 ON TableName1.id = TableName2.id WHERE TableName1.column3 = ‘Yes’
SELECT column_name1, column_name2, column_nameN FROM table_name ORDER BY RAND() LIMIT 20; Another way that is more efficient way of selecting random rows in MySQL SELECT column_name1, column_name2, column_nameN FROM table_name AS t1 JOIN (SELECT id FROM table_name ORDER BY RAND() LIMIT 20) as t2 ON t1.id=t2.id;
GROUP_CONCAT function is used to concatenate multiple rows into a single field in MySQL. SELECT student_id, GROUP_CONCAT(subject_name SEPARATOR ‘, ‘) FROM student_subject GROUP BY student_id; Distinct Operator can be used to avoid duplicates. SELECT student_id, GROUP_CONCAT(distinct subject_name SEPARATOR ‘, ‘) FROM student_subject GROUP BY student_id; Order By Clause can be used to keep the values … Read more
Find All Tables in MySQL With Specific Column Names in specific schema. SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN (‘columnName1′,’columnName2′, columnNameN) AND TABLE_SCHEMA=’Database Name’; Find All Tables in MySQL With Specific Column Names in all schemas. SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN (‘columnName1′,’columnName2’, columnNameN)
Get all Column of a specific Tables in MySQL. SELECT table_name as ‘Table Name’, column_name as ‘Column Name’ FROM information_schema.COLUMNS WHERE table_schema = ‘$DATABASE_NAME’; AND table_name = ‘$TABLE_NAME’; Get all Column of some Tables in MySQL. SELECT table_name as ‘Table Name’, column_name as ‘Column Name’ FROM information_schema.COLUMNS WHERE table_schema = ‘$DATABASE_NAME’; AND table_name in (‘$TABLE_NAME1’, … Read more
Get all Tables of a specific schema in MySQL. SELECT table_name as ‘Table Name’ FROM information_schema.TABLES WHERE table_schema = ‘$DATABASE_NAME’; Get all tables of all schemas in MySQL. SELECT table_name as ‘Table Name’ FROM information_schema.TABLES;
Get Record Count of a specific Table in MySQL. SELECT SUM(TABLE_ROWS) as ‘Total Record Count’ FROM information_schema.TABLES WHERE table_schema = ‘$DATABASE_NAME’ AND table_name = ‘$TABLE_NAME’; Get Record Count of all Tables of a specific schema in MySQL. SELECT SUM(TABLE_ROWS) as ‘Total Record Count’ FROM information_schema.TABLES WHERE table_schema = ‘$DATABASE_NAME’; Get Record Count of all Tables … Read more