0

I'm pretty new to SQL and need some help configuring a command. The details of my database structure can be found in this thread: How to copy new data but skip old data from 2 tables in MySQL The general problem is that I'm merging a new (temporary) database with an old one. I want to keep all the data in the old but copy over any new data from the new. If there is a duplicate, the old should be favored/kept.

My current command is:

INSERT INTO BAT_players
SELECT * 
FROM bat2.bat_players
WHERE NOT EXISTS (SELECT 1 FROM  BAT_players WHERE BAT_players(UUID) = bat2.bat_players(UUID));

When I run this, I get Function bat2.bat_players undefined or Function bat.BAT_players undefined

I do not know how to proceed and would appreciate the help.

Matthew
  • 1
  • 2
  • 1
    What are `BAT_players(UUID)` and `bat2.bat_players(UUID)` supposed to do? You're calling them like functions. – Cully May 16 '21 at 06:40

2 Answers2

1

Columns are accessed using . not parens:

INSERT INTO BAT_players
    SELECT * 
    FROM bat2.bat_players bp2
    WHERE NOT EXISTS (SELECT 1
                      FROM BAT_players bp
                      WHERE bp.UUID = bp2.UUID
                     );

Note that the columns have to correspond by position, because you are not explicitly listing them. As a general rule, you want to list all all the columns in an insert:

INSERT INTO BAT_players ( . . . )
    SELECT . . .
    . . .
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
-1

I am no familiar with the idea of MySQL,

I worked with SQL Server to be honest but if all the infrastructure are the same and I say IF, then there is a trick to these kinds of transactions between databases and that's simply the phrase dbo.

Like below:

using BAT
Insert into bat_players
SELECT * FROM bat2.dbo.bat_players

and also the rest of your conditions

or

instead of using the phrase using bat you can simply add the dbo to:

Insert into bat.dbo.bat_players

and again the rest of your condition, just remember to use the dbo before each [table name].

HUGE UPDATE

if you want to access the fields (columns) you have to use . as @Gordon Linoff explained above. For example:

...
Where bat2.dbo.bat_players.UUID = --the condition--
Atrin Noori
  • 311
  • 3
  • 12
  • The OP is using `bat2` as an alias of `bat_players`. Also, you're not addressing the error the OP is getting, and you aren't taking into account the fact that the OP wants to "merge" the two tables. – Cully May 17 '21 at 00:36
  • @Cully did you see the sentence "rest of your conditions"?? meaning which ever condition is necessary to "merge" the two tables Secondly by the origin of his database model in the link given above "bat2" is another database entirely third... the problem was addressed by another user about the error – Atrin Noori May 17 '21 at 05:48
  • Oh yeah, you're right about bat2. I didn't downvote you, btw. – Cully May 17 '21 at 06:59
  • @Cully Its ok brother your comment made realize that I needed to change some parts of my answer and for that I thank you – Atrin Noori May 17 '21 at 14:06