CODEWITHSUNDEEP
mysqlsql
Eloy Fernández Franco
Eloy Fernández Franco

Reputation: 1372

Insert rows on table from the same table

I have a table on my database, where I have this columns: [id, userId, locationId, created, update], and what I want to do is insert all users from a location to other location, except the users that exists on the second location (and if this new entries could have the date of this new insert on the columns created and updated, would be fantastic). I mean, if I had this table:

id | userId | location | created | updated
 1 |      1 |        1 | 1234567 | 1234567
 2 |      2 |        1 | 9876543 | 9876543
 3 |      1 |        2 | 5555555 | 6666666

Assuming that the actual time is 9999999, after the insert operation, the result must to be this:

id | userId | location | created | updated
 1 |      1 |        1 | 1234567 | 1234567
 2 |      2 |        1 | 9876543 | 9876543
 3 |      1 |        2 | 5555555 | 6666666
 4 |      2 |        2 | 9999999 | 9999999

Any solution?? Thanks.

EDIT (considering more cases)

There is some more information that I hadn´t considered. On the example, there is a transfer of data in only one direction: the info goes from location 1 to location 2. Mostly, because there was no user on the location 2 that location 1 didn´t have.
The perfect solution would be that the transfer went in two directions, or even using more than 2 locations, something like this:

id | userId | location | created | updated
 1 |      1 |        1 | 1234567 | 1234567
 2 |      2 |        1 | 9876543 | 9876543
 3 |      1 |        2 | 5555555 | 6666666
 4 |      3 |        2 | 1829384 | 0192837
 5 |      4 |        3 | 8888833 | 9991828
 6 |      5 |        4 | 1111111 | 2222222

Assuming that the actual time is 9999999, AND I want to involve only the locations 1, 2 and 3, after the insert operation, the result must to be this:

id | userId | location | created | updated
 1 |      1 |        1 | 1234567 | 1234567
 2 |      2 |        1 | 9876543 | 9876543
 3 |      1 |        2 | 5555555 | 6666666
 4 |      3 |        2 | 1829384 | 0192837
 5 |      4 |        3 | 8888833 | 9991828
 6 |      5 |        4 | 1111111 | 2222222
              /*news*/
 7 |      3 |        1 | 9999999 | 9999999
 8 |      4 |        1 | 9999999 | 9999999
 9 |      2 |        2 | 9999999 | 9999999
10 |      4 |        2 | 9999999 | 9999999
11 |      1 |        3 | 9999999 | 9999999
12 |      2 |        3 | 9999999 | 9999999
13 |      3 |        3 | 9999999 | 9999999

The location 1, 2 and 3 share their users, but not the location 4. This could be the best solutions, BUT I would settle for that the locations share their data with the others one by one... and programmatically I can make a loop.

Upvotes: 0

Views: 58

Answers (3)

Shadow
Shadow

Reputation: 34232

What I would do I to create a multi column unique index on userid - location fields. This would prevent duplicate user ids for the same location to be inserted.

Then use an insert ignore ... select ... query to insert the users from the other location. Ignore means that MySQL will ignore duplicate key violation errors and will carry on inserting the valid users:

INSERT IGNORE INTO yourtable (userId, location, created, update)
SELECT userId, new_location, now(), now()
FROM youtable as t2
WHERE t2.location=current_location

new_location and current_location are parameters that you need to provide in the query.

Upvotes: 1

PravinS
PravinS

Reputation: 2584

Use INSERT ....SELECT Statement

For created and update date you can use MySQL UNIX_TIMESTAMP() function

Like this

INSERT INTO LOCATION2 (id, userId, locationId, created, update)
SELECT id, userId, locationId, UNIX_TIMESTAMP(), UNIX_TIMESTAMP() FROM LOCATION1

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use a query like the following:

INSERT INTO mytable  (`userId`, `location`, `created`, `updated`)
SELECT userId, IF(location = 1, 2, 1), NOW(), NOW()
FROM mytable 
GROUP BY userId 
HAVING COUNT(DISTINCT location) = 1;

The query assumes that id field is auto-increment and can thus be excluded from the INSERT operation. It also assumes that there are only two available location values, namely 1 and 2.

Upvotes: 0

Related Questions