Tag Archives: mysql

Delete records from multiple tables

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;

OR

DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id;

Reference

https://dev.mysql.com/doc/refman/8.0/en/delete.html (refer section –┬áMulti-Table Deletes)

Reset root password for MySQL on windows

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';
FLUSH PRIVILEGES;

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

>c:\mysql\bin\mysqld --init-file=c:\\mysql-init.txt

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.

MySQL database auto backup on windows

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
:: 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
DEL script.ftp
:: DONE FTP backup file to remote server

Update with join table

To update a table with a condition within other table (to join table), you can use this statement

1
2
3
UPDATE table1 a, table2 b
SET a.field6 = value
WHERE a.field1 = b.field1 AND b.field2 = value2

MySQL insert into select

This SQL statement to be used to insert data into a table from another different table.

1
2
3
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.