By vizzdoom


2010-04-19 16:38:59 8 Comments

I have two tables. There are users informations from two sites:
p_users
p_users2
There are 3726 users in first and 13717 in second.

Some users in p_users2 are in p_users. I want merge this two tables to the one big table - but rows with same usernames can't be doubled.

How can I do this? I tried something like this:

DELETE FROM p_users2 WHERE user_id IN 
(
select p.user_id from p_users p
join p_users2 p2 on p.username=p2.username
)

After that I should receive a table with unique usernames, which I want to export and import to the first one. But when I execute my query I got error:

SQL Error (1093): You can't specify target table 'p_users2' for update in FROM clause. (MYSQL)

4 comments

@knittl 2010-04-19 16:52:04

only insert users from the second table where there is no matching student in the first table

INSERT INTO p_users
SELECT * FROM p_users2 p2
WHERE NOT EXISTS (
  SELECT * FROM p_users p1
  WHERE p1.id = p2.id
)

@Kibbee 2010-04-19 16:47:33

Try this

DELETE p2 FROM p_pusers2 AS P2
INNER JOIN p_users p1
ON p1.username=p2.username

@SeanJA 2010-04-19 16:46:11

Create a new table where the username is unique, then do an Insert Ignore... see:

How can I merge two MySQL tables?

@RDL 2010-04-19 16:45:02

Do them as two separate statements. First delete the duplicates with:

DELETE FROM p_users2 WHERE user_id IN 
(select p.user_id from p_users p)

Then use the INSERT with SELECT statement:

INSERT INTO P_USERS (FIELD1, FIELD2, FIELD3) SELECT FIELD1, FIELD2, FIELD3 FROM P_USERS2

Related Questions

Sponsored Content

47 Answered Questions

[SOLVED] How do I import an SQL file using the command line in MySQL?

37 Answered Questions

[SOLVED] Should I use the datetime or timestamp data type in MySQL?

15 Answered Questions

[SOLVED] MySQL Error 1093 - Can't specify target table for update in FROM clause

15 Answered Questions

[SOLVED] How to get a list of user accounts using the command line in MySQL?

5 Answered Questions

[SOLVED] Deleting rows with MySQL LEFT JOIN

2 Answered Questions

[SOLVED] Error 1093 when trying to perform delete in MySQL

  • 2018-03-23 15:36:25
  • Poohbreezy
  • 51 View
  • 0 Score
  • 2 Answer
  • Tags:   mysql

1 Answered Questions

Mysql delete by primary key with select(error 1093)

2 Answered Questions

[SOLVED] Can't specify target table for update, delete query in mysql

4 Answered Questions

Sponsored Content