0

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.

Improve this question

3 Answers 3

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks @Shadow, I am a little afraid because this is a legacy project, and until now this table didn´t use this unique index... I think I have covered all possibilities programmatically (with php), but I am going to ask a little more about the Giorgios answer in first instance, and if I am not able to understand, I am going to make local tests before upload. Thank you.
If you want to keep userid - location pairs unique, then the easiest way is to declare a unique index on these 2 fields. Otherwise, you need to ensure in each and every part of the code that you check the uniqueness of the data before updating the table.
0

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.

Improve this answer

3 Comments

I believe you need to alias mytable in the select part, otherwise it is not going to work.
Hello @Giorgios, I am not sure about if I understand somethings on your answer, fo example: what is the meaning of 1, 2, 1 in "IF(location = 1, 2, 1)"?? What happens if there are more available location, but I only want to merge only two? and if I would merge more than two locations? and last question What is the direction of the merge?? I make an edit to provide more info, If you could beat him out, please. Thanks.
If you have more than 2 locations, then Giorgos' solution will not work because it is going to merge all other locations to the one in the condition of the if. As described his answer, he assumed that you only had 2 locations. The if() function is described in the MySQL documentation.
0

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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.