how to copy only distinct values from one table to another in Mysql?

Question

I have a MySql database round-about 2.5GB,

I have just created another table[b] having columns only |query| and |date|

I want to insert all the distinct records in query column, with it's respective date, from Table[A] to [B], is there any fast query?

so you want unique values in query column? what if there are multiple rows with same query and different (or same) dates? Or perhaps you want unique combination of the two columns? — Gurwinder Singh, Sep 22 '17 at 12:02
sir. only unique values in query column.with its repected date. — Shafi ullah, Sep 22 '17 at 12:46

score 7 · Accepted Answer · answered Sep 22 '17 at 12:01

7

Use INSERT INTO ... SELECT:

INSERT INTO Tableb(query, date)
SELECT query, MAX(Date) AS MAXDate
FROM Tablea
GROUP BY query

This will give you distinct query with the most recent date.

answered Sep 22 '17 at 12:01

@Shafiullah - please post the full error you got. The query I posted shouldn't give a syntax error it is correct. – Sep 22 '17 at 12:48
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 'unique(query,date) select query, max(date) As maxdate from 500k group by query' at line 1 – Shafi ullah Sep 22 '17 at 13:08
@Shafiullah - There is no `unique(query,date)` in my query. It is something you added by yourself. Can you please update your question with the full query you are trying to execute? – Sep 22 '17 at 13:09

score 0 · Answer 2 · edited Sep 22 '17 at 13:24

0

You can use

insert into table[b](query,date)
select query,date from table[a] order by table[a] asc

edited Sep 22 '17 at 13:24

Alejandro

answered Sep 22 '17 at 12:04

Rishav Prasher

sir but TableA has many repeted values . i just want only distinct of query column with its respected date . – Shafi ullah Sep 22 '17 at 12:48
select distinct query,date from table[a] – Rishav Prasher Sep 22 '17 at 12:54

score 0 · Answer 3 · edited Jun 25 '19 at 08:30

0

INSERT INTO tableB
SELECT * FROM tableA
group by query

Note: please remove id from both the tables while applying the above query.

edited Jun 25 '19 at 08:30

adiga

answered Jun 25 '19 at 08:28

Solomon Suraj

3 Answers3