CODEWITHSUNDEEP
sqliteduplicates
Sergey Ronin
Sergey Ronin

Reputation: 776

'merge' rows if they are duplicated in a table - SQLite

Table is the following:

CREATE TABLE UserLog(uid TEXT, clicks INT, lang TEXT)

Where uid field should be unique.

Here is some sample data:

|      uid      |  clicks |    lang    |
----------------------------------------
| "898187354"   |    4    |    "ru"    |
| "898187354"   |    4    |    "ru"    |
| "123456789"   |    1    |   <null>   |
| "123456789"   |    10   |    "en"    |
| "140922382"   |    13   |   <null>   |

As you can see, I have multiple rows with where the uid field is now duplicated. I would like for those rows to be merged in a following way:

clicks fields are added, and lang fields are updated if their previous value was null.

For the data shown above, it would look something like this:

|      uid      |  clicks |    lang   |
---------------------------------------
| "898187354"   |    8    |    "ru"   |
| "123456789"   |    11   |    "en"   |
| "140922382"   |    13   |   <null>  |

It seems that I can find many ways to simply delete duplicate data, which I do not necessarily want to do. I'm unsure how I can introduce logic in SQL statements that does this.

Upvotes: 0

Views: 42

Answers (1)

forpas
forpas

Reputation: 164079

First update:

update userlog
set 
  clicks = (select sum(u.clicks) from userlog u where u.uid = userlog.uid),
  lang = (select max(u.lang) from userlog u where u.uid = userlog.uid)
where not exists (
  select 1 from userlog u
  where u.uid = userlog.uid and u.rowid < userlog.rowid
);

and then delete the duplicate rows that are not needed: 

delete from userlog
where exists (
  select 1 from userlog u
  where u.uid = userlog.uid and u.rowid < userlog.rowid
);

Upvotes: 1

Related Questions