To disable ONLY_FULL_GROUP_BY mode you can use the following command
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Or can update in phpmyadmin by clicking on “Variables” tab and look for sql_mode. Click on Edit to remove ONLY_FULL_GROUP_BY
This is sample query to delete record from multiple tables.
Specify all joined tables to get the list but can specify records in which tables to be deleted.
DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id;
DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id;
https://dev.mysql.com/doc/refman/8.0/en/delete.html (refer section – Multi-Table Deletes)
SQL to check a value exist in one table but not in another.
FROM table1 t1
LEFT JOIN table2 t2 ON t2.name = t1.name
WHERE t2.name IS NULL
or this (not tested)
WHERE NOT EXISTS (SELECT 1
WHERE A.ID = B.ID)
To secure the data by encryption at data at database level. This will be full done by database application and won’t affect the application level.
Almost all popular RDBMS provide this feature but maybe with enterprise edition.
- Azure SQL
If you have problem accessing your MySQL database with this error
ERROR 1045: Access denied for user: ‘root@localhost’ (Using password: NO)
You can try reset your root password with the following steps:
1. Create a txt file with this content
UPDATE mysql.user SET Password=PASSWORD('newpassword') WHERE User='root';
2. Save the file as mysql-init.txt in c:\ (or any directory you want)
3. Stop MySQL service
4. Start MySQL service in command prompt with this command line
5. Remove the txt file in created in step 2
Note: You can/need to specify correct path for the file and mysql directory for step 2 and 4.
To backup mysql database (all databases) can use this batch file
Then you can compile the .bat file to become .exe file so that the password is hidden.
Download bat to exe converter from CNET download
Lastly, you can include the exe file to run in task scheduler
You can have option to upload the file via FTP to other server
:: Start FTP files to remote server
:: Create the temporary FTP script file
> script.ftp ECHO ftpusername
>>script.ftp ECHO ftppassword
>>script.ftp ECHO lcd /
>>script.ftp ECHO lcd /MySQLBackups/backupfiles
>>script.ftp ECHO binary
>>script.ftp ECHO prompt n
>>script.ftp ECHO put FullBackup.%backupdate%.zip
>>script.ftp ECHO bye
:: Use the temporary script for unattended FTP
:: Note: depending on your OS version you may have to add a '-n' switch
FTP -v -s:script.ftp ftphostcom.com
:: Overwrite the temporary file before deleting it
TYPE NUL >script.ftp
:: DONE FTP backup file to remote server
To update a table with a condition within other table (to join table), you can use this statement
UPDATE table1 a, table2 b
SET a.field6 = value
WHERE a.field1 = b.field1 AND b.field2 = value2
This SQL statement to be used to insert data into a table from another different table.
INSERT INTO table1 (field1)
SELECT table2.field2 FROM table2
WHERE table2.field2 > 100;
Be careful with ambiguous fields. So put the table name in front of the field name.
Also be careful with duplicate data inserted to the table.
If you want to update all records can use this sql statement
UPDATE staff SET `staff_name` = UPPER( `staff_name` )