Mysql: Find and remove duplicates

Find and remove duplicates.

CREATE TABLE mytable(
  rowid INT(11) NOT NULL,
  msgid INT(11) DEFAULT NULL,
  userid INT(11) DEFAULT NULL,
  PRIMARY KEY (rowid)
);
INSERT INTO mytable VALUES 
  (1, 5, 33),
  (2, 5, 12),
  (3, 4, 21),
  (4, 5, 33),
  (5, 5, 33),
  (6, 4, 15),
  (7, 4, 21);

Select duplicates:

SELECT t1.* FROM mytable t1
  JOIN (
        SELECT msgid, userid, MIN(rowid) min_rowid FROM mytable
          GROUP BY msgid, userid
        ) t2
  ON t1.rowid <> t2.min_rowid AND t1.msgid = t2.msgid AND t1.userid = t2.userid;
+-------+-------+--------+
| rowid | msgid | userid |
+-------+-------+--------+
|     4 |     5 |     33 |
|     5 |     5 |     33 |
|     7 |     4 |     21 |
+-------+-------+--------+

Remove duplicates with a DELETE statement:

DELETE t1 FROM mytable t1
  JOIN (
        SELECT msgid, userid, MIN(rowid) min_rowid FROM mytable
          GROUP BY msgid, userid
        ) t2
  ON t1.rowid <> t2.min_rowid AND t1.msgid = t2.msgid AND t1.userid = t2.userid;

Remove duplicates with a new unique key:

ALTER IGNORE TABLE mytable
  ADD UNIQUE KEY(msgid, userid);

This ALTER TABLE with IGNORE keyword will create new unique key and remove all duplicates from the table in a one step.

SELECT * FROM mytable;
+-------+-------+--------+
| rowid | msgid | userid |
+-------+-------+--------+
|     1 |     5 |     33 |
|     2 |     5 |     12 |
|     3 |     4 |     21 |
|     6 |     4 |     15 |

Mysql: Copy data from one tabe to another (update existed records)

Example shows how to update records in one table with data from another table.

CREATE TABLE table_a (
  id INT(11) DEFAULT NULL,
  column_a INT(11) DEFAULT NULL
);
CREATE TABLE table_b (
  id INT(11) DEFAULT NULL,
  column_b INT(11) DEFAULT NULL
);
INSERT INTO table_a VALUES 
  (1, 10),
  (2, 20),
  (3, 30),
  (4, NULL),
  (5, 0);
INSERT INTO table_b VALUES 
  (1, 1),
  (2, NULL),
  (3, 0),
  (4, 4),
  (5, 5);

Update data:

UPDATE table_a a
  JOIN table_b b
    ON a.id = b.id
SET
  b.column_b = a.column_a;

Result table:

SELECT * FROM table_b;
+------+----------+
| id   | column_b |
+------+----------+
|    1 |       10 |
|    2 |       20 |
|    3 |       30 |
|    4 |     NULL |
|    5 |        0 |
+------+----------+

Mysql cross table join

2 mysql table as follow:
items

id item_name user_id
1    test1     1    
2    test2     1
3    test3     1
4    test4     1  

project

id user_id items
1    1       1,3
2    1       2,4

how can write a join query that can return each items in a project?

project1 =>
    item1=>
      [id1] =>
      [name1] =>
    item3=>
      [id3] =>
      [name3] =>

First of all don't store strings of delimited values in your db. You're limiting your self with the means to normally maintain and query data. Normalize your data (in this case by introducing project_items table with project_id and item_id columns). It'll pay off big time in a long run.
In the mean time you can use FIND_IN_SET() to join your tables

SELECT p.id project_id, p.user_id, i.id item_id, i.item_name 
   FROM project p LEFT JOIN items i
     ON FIND_IN_SET(i.id, p.items) > 0
    AND p.user_id = i.user_id
 ORDER BY p.id, i.id

Output:

| PROJECT_ID | USER_ID | ITEM_ID | ITEM_NAME |
----------------------------------------------
|          1 |       1 |       1 |     test1 |
|          1 |       1 |       3 |     test3 |
|          2 |       1 |       2 |     test2 |
|          2 |       1 |       4 |     test4 |

UPDATE: Values of items should not contain spaces. Either remove them or use REPLACE() like this
ON FIND_IN_SET(i.id, REPLACE(p.items, ' ', '')) > 0