Category Archives: database

Update with join table

Leave a reply

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

Leave a reply

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.

Update with select statement

Leave a reply 
1
2
3
4

UPDATE student_transaction 
SET studentid = 
(SELECT newstudentid FROM student_newid 
WHERE studentid = student_transaction.studentid)

Replace line break with

Leave a reply

This code is used to replace line break saved in database to
in html display

$astring = str_replace(chr(13) . chr(10), "", $astring);

Updated: Actually there is a built in function for this as follow

echo nl2br($astring);

Problem uploading big sql file via phpMyAdmin

Leave a reply

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.

SQL statement to list duplicate contents

Leave a reply

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 )

source

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)

source

How to backup MySQL table in phpMyAdmin

2 Replies

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).

phpmyadmin

After you done with the operation and it is successful, you can easily drop the temporary backup table.