[Level 2] How to delete duplicate data in MySQL. 

drop table if exists abc;
create table abc (i int DEFAULT NULL);
insert into abc values (1),(2),(2),(3),(3),(3),(NULL),(NULL),(NULL),(NULL);
-- insert into abc values (),(),(),(); -- work
-- insert into abc values (1),(),(),(),(); -- not work
-- ERROR 1136 (21S01): Column count doesn't match value count at row 2

-- case 1:
delete abc from abc, (select * from abc group by i having count(*)>1) as xyz where abc.i=xyz.i or (abc.i is NULL and xyz.i is NULL) limit 1;
-- case 2:
delete abc from abc, (select * from abc group by i having count(*)>1) as xyz where abc.i=xyz.i or (abc.i is NULL and xyz.i is NULL);

/* result:
mysql> -- case 1:
mysql> delete abc from abc, (select * from abc group by i having count(*)>1) as xyz where abc.i=xyz.i or (abc.i is NULL and xyz.i is NULL) limit 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'limit 1' at line 1
mysql> -- case 2:
mysql> delete abc from abc, (select * from abc group by i having count(*)>1) as xyz where abc.i=xyz.i or (abc.i is NULL and xyz.i is NULL);
Query OK, 9 rows affected (0.00 sec)
*/


/*
easy way to delete duplicate data.
create table cba as select distinct from abc;
drop table abc;
rename table cba to abc;
*/
Wish this helps.
regards,
Stanley Huang

Comments

Post a Comment

Popular posts from this blog

[Level 1] Rar tool for Solaris.

Sometimes we will got a rar compressed file from our friends, but there is no default tool in OpenSolaris to decompress the rar filess. We can got a Solaris Intel version from rarlab . The steps as the following: 1. down load the rar tools from rarlab. http://www.rarlab.com/rar_add.htm 2. uncompressed the source. # gunzip ./unrar-sol-intel.gz 3. move it to the location you used to. # mv /unrar-sol-intel.gz /usr/bin/unrar 4. try to decomprss # unrar x ./test.rar Wish this helps. regards, Stanley Huang
Read more

[Level 2] iif in Python

If you want to implement iif in Python, please refer to the following sample code: #!/bin/env python def iif(b): return 'It\'s True' if b else 'It\'s False' print iif(True) print iif(False) And the following sample, guess what will you get! #!/bin/env python b = True print 'b: %s' % 'Ture' if b else 'False' b = False print 'b: %s' % 'Ture' if b else 'False' Result: $ ./iif.py b: Ture False The second result is "False", not "b: False". Why??? Because the prededence of string concatenation is higher than if. Therefore you need to use () to make it correct. #!/bin/env python b = True print 'b: %s' % 'Ture' if b else 'False' b = False print 'b: %s' % ('Ture' if b else 'False') Wish this helps. regards, Stanley Huang
Read more