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.
If you want to update all records can use this sql statement
1 | UPDATE staff SET `staff_name` = UPPER( `staff_name` ) |
Here is the link where you can download IP to country sql for database. Also come with some sample codes on how to use the data. Might be useful for your program
I faced problem uploading a huge sql file to my shared hosting server. The size was not that big. It was only 1.8MB in size with about 30,000 records but phpMyAdmin (provided in cpanel) still failed to fully upload and run the file.
I then googled for a solution.
One of the solutions suggest to use SSH but I’m not familiar with it.
Another solution is by using mysqldumper but the configuration seems quite tedious.
Then I found another solution called BigDump which is very straight forward. You upload a PHP script together with your huge sql file via FTP. Then just run the PHP script as usual. In a second all the data has been restored in your database.
You will need this SQL statement to list all duplicate contents in a table
SELECT email, COUNT(email) AS NumOccurrences FROM users GROUP BY email HAVING ( COUNT(email) > 1 ) |
If you want to list all duplicate records
SELECT t1.* FROM `some_table` t1, ( SELECT documentno, COUNT(1) AS dup FROM `some_table` GROUP BY documentno HAVING dup > 1 ) AS t2 WHERE t1.documentno = t2.documentno; |
Then how to remove the duplicate records?
1. Create new table and insert clean records (without duplicate)
CREATE TABLE new_table AS SELECT * FROM old_table WHERE 1 GROUP BY [COLUMN TO remove duplicates BY]; |
Note: Be careful, this will create a table without primary key, auto-increment etc. So make sure you update your table after run this statement
2. Drop old table (with duplicates)
Sometimes when you are going to execute an SQL statement to your production database, you need to be very careful especially when you are using DELETE or UPDATE command.
To ensure your original data will be available, you can backup the whole table that you are going to do the operation.
To backup the whole table is very easy in phpMyAdmin (even in other SQL tool).
After you done with the operation and it is successful, you can easily drop the temporary backup table.
This is how to do daily mysql backup with cron job in cpanel. This cron job will backup your mysql database as dump file (and gzip) daily. The files will be replaced on the same day next week.
Command to run to dump your mysql database, gzip it and replace it the following week as follow
1 | mysqldump -uDBUSERNAME -pPASSWORD --opt DBNAME > /home/USER/dbbackup/FILENAME.sql; gzip -f /home/USER/dbbackup/FILENAME.sql |
Replace DBUSERNAME, PASSWORD, DBNAME, USER, FILENAME respectively.
Sometimes you may need to populate a table (usually a temporary or new table) from data of another table (usually huge table).
Here’s is the statement that you may use
INSERT INTO “table1” (“column1”, “column2”, …)
SELECT “column3”, “column4”, …FROM “table2”
I got this error message in my application
Error with database: Table ‘table_name’ is marked as crashed and should be repaired
Went to the net and look for the solution. The simplest one is to click on the “Repair Database” button provided in cpanel
If you want to update multiple records, usually you will have to do a loop and generate multiple SQL statement like this
UPDATE table_name SET fieldname = value1 WHERE fieldname = field_id1;
UPDATE table_name SET fieldname = value2 WHERE fieldname = field_id2;
UPDATE table_name SET fieldname = value3 WHERE fieldname = field_id3;
But to save some resource (perhaps), you can also update multiple records in just one single MySQL statement
UPDATE table_name
SET fieldname = CASE value_id
WHEN value_id1 THEN ‘value1’
WHEN value_id2 THEN ‘value2’
WHEN value_id3 THEN ‘value3’
END
WHERE value_id IN (value_id1,value_id2,value_id3)