0

I am very new to SQL (like today new) and I have a question. I'm trying to copy data from one table into another table. I know how to do this from the internet but my issue is, I need to skip any data that is already there. Here's my scenario. We have monitoring software that logs new users every time it encounters one. We have an old database with thousands of users that we use as our database, but we had to switch to a different database for a short time due to a server outage. This means that we have some users on the new database, but most on the old. There are also duplicates since everyone that was logged in the old had to be re-logged in the new when the software encountered a 'new user' that wasn't in the new database.

What I want to do is copy all the new users in the new database over to the old database, but skip any users that are already in the old database.

How can this be done? Sorry for the long confusing paragraph. Please remember I'm very new to SQL and don't know squat. Thanks in advance.

Edit: more info: Here is how my tables are set up. I have 2 databases 'bat' and 'bat2'. bat2 is the new one, bat is the old one.

+---------------+
| Tables_in_bat |
+---------------+
| BAT_ban       |
| BAT_kick      |
| BAT_mute      |
| BAT_players   |
| BAT_web       |
| bat_ban       |
| bat_comments  |
| bat_kick      |
| bat_mute      |
| bat_players   |
| bat_web       |
+---------------+

+----------------+
| Tables_in_bat2 |
+----------------+
| BAT_ban        |
| BAT_kick       |
| BAT_mute       |
| BAT_players    |
| bat2_comments  |
+----------------+

I will need to merge BAT_players from the new one into BAT_players in the old one. the lowercase tables are a different story so ignore those.

The structure of the BAT_players table looks like this:

+------------------+----------------------------------+-----------------+---------------------+---------------------+
| BAT_player       | UUID                             | lastip          | firstlogin          | lastlogin           |
+------------------+----------------------------------+-----------------+---------------------+---------------------+
| 00meow9          | 21826e64a2974911a0fe542bf11e3901 | 73.163.22.154   | 2021-05-06 10:49:49 | 2021-05-06 10:49:49 |
| 0800Nummer       | bb7bd784c7804834b2df56a6c76d53da | 85.250.79.8     | 2021-05-04 13:43:28 | 2021-05-08 08:04:33 |
| 1angelfish       | 6c7ebb3197d04d71a7976bd830abe452 | 68.131.116.38   | 2021-05-03 20:46:36 | 2021-05-07 17:32:15 |

The UUID is something that will stay consistent throughout the 2 files.

Matthew
  • 1
  • 2
  • Please show your table structure and as far as you've gotten in the query. Something like Insert into x Select * from y where y.id not in (select id from x) might be expedient. – Missy May 12 '21 at 14:37
  • I haven't gotten anywhere in the query yet but I will add my structure. – Matthew May 12 '21 at 17:45
  • Related: https://stackoverflow.com/questions/425158/skip-certain-tables-with-mysqldump – Jesse Nickles Jul 27 '22 at 08:05

1 Answers1

0

What I want to do is copy all the new users in the new database over to the old database, but skip any users that are already in the old database.

You need one or more columns to specify what a "user" is. One method if you have a user_id is:

insert into old ( . . . )      -- list the columns here
    select . . .
    from new n
    where not exists (select 1 from old o where o.user_id = n.user_id);

MySQL has convenient construct, if you have a unique index or constraint on user_id (or whatever columns define the user):

insert into old ( . . . )
    select . . .
    from new n
    on duplicate key update set user_id = values(user_id);

The most recent versions have deprecated VALUES() to you might get a warning.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • There is a unique ID that should be the same throughout both databases so I could use that. I will let you know if I have any issues. – Matthew May 11 '21 at 19:31
  • For the line: `where not exists (select 1 from old o where o.user_id = n.user_id);`, How do I specify the user id for each table? (specifically the 'o.user_id' and 'n.user_id' parts are) I tried `database1.User_data.entity' and 'database2.User_data.entity' but it just gives an error. Am I doing it wrong? 'entity' is the column that has the unique IDs – Matthew May 11 '21 at 20:41