sql – Laurent Hinoul

How to delete duplicate rows in SQL

28/01/2016 • 0 Comments

To find and delete duplicate rows from a table, you can execute the query below. Note that this is for Oracle. You can use it for other databases but you need to change the ‘rowid’ then.

delete from
   table_name A
where
  a.rowid >
   any (
     select B.rowid
     from table_name B
     where A.column_name = B.column_name
   );

1

2

3

4

5

6

7

8

9

delete from

table_name A

where

a.rowid >

any (

select B.rowid

from table_name B

where A.column_name = B.column_name

);

You need to change the ‘table_name’ by your table name an the ‘column_name’ by your column you want to check on. To explain…

How to find a column name in all tables in MySQL

24/07/2014 • 0 Comments

Sometimes, it could be handy to know in which tables a certain column name is specified. To do so, use the query below.

select distinct table_name
    from information_schema.columns
    where column_name in ('columnA','ColumnB')
        and table_schema = 'YourDatabase';

1

2

3

4

select distinct table_name

from information_schema.columns

where column_name in ('columnA','ColumnB')

and table_schema = 'YourDatabase';

Note: you need to fill in your own column names and table_schema. Enjoy!

How to fix postgres error: current transaction is aborted, commands ignored until end of transaction block

13/06/2014 • 2 Comments

In this post, I will tell you how to fix the error that PostgreSQL throws when you try to execute an SQL-statement.