This error message is not helpful at all. There are similar error to this for DOUBLE etc instead of DECIMAL value.
I got this error when running a simple query like below in MySQL.
update table_1
set field_value = “value”
where field_id in (123, 345, 456);
This caused by the value in where condition compared to a non-integer value. For example, there are some empty string in field_id field.
The solution – changed the string in field_id to NULL or integer value.
If you are to insert hundreds or thousands of records, please use bulk insert. Avoid call insert query in loop.
$data = array(
array(
'title' => 'My title' ,
'name' => 'My Name' ,
'date' => 'My date'
),
array(
'title' => 'Another title' ,
'name' => 'Another Name' ,
'date' => 'Another date'
)
);
$this->db->insert_batch('mytable', $data);
There will be limit to how many records can be added at one go.
Some limitations
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;
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)
SQL to check a value exist in one table but not in another.
SELECT t1.name
FROM table1 t1
LEFT JOIN table2 t2 ON t2.name = t1.name
WHERE t2.name IS NULLor this (not tested)
SELECT *
FROM B
WHERE NOT EXISTS (SELECT 1
FROM A
WHERE A.ID = B.ID)
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.
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 |
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 |