Morning

Hot enough?

Morning

30-ish

Im currently trying to convert parts of my "old" tables... therefore i need to iterate over all "players" in order to generate a unique ID for each of them to insert them into another table... if the generated unique ID already exists in the other table, it should try it as long as it found one that fits... i never did that before in SQL, any tipps where i could begin ?

@genaray Not terribly clear, sorry. You have an old table and you have a new table. Rows from the old table need to be moved to the new table. But the new table already has some rows. Because of that, the old rows cannot simply be inserted, because their IDs may conflict with the IDs of rows already existing in the new table. So new IDs need to be generated for those (= the old rows with conflicting IDs). But when there's no conflict, old IDs may (or should?) be kept. Is that what's happening?

@AndriyM Thanks for your time and sorry that it wasnt that clear... but yeah, thats exactly the case :)

@genaray And are you asking how to generate the new IDs?

@AndriyM This and how i can achieve that it tries it with a new id till it was inserted sucessfully

I'm not sure I understand this part: "how i can achieve that it tries it with a new id till it was inserted sucessfully"

How are you trying (or planning or expecting) to do this now?

@AndriyM Well, it should try to insert the item into the new table till it was sucessfull... if the id already exists, it should generate a new one and repeat the insertation

@AndriyM I have no clue... im a game engineer :/ i only had database lessons about the basics... Thats why i asked my question here

Is the ID column in the new table an auto-generated column of some sort (as in auto-increment or something)?

Probably MySQL based on previous questions

@AndriyM It is... but each entity already own a unique bigint primary key... the highest one is already somewhere near the limit, so insertation with auto generated keys is probably not the best decision, it may exhaust its limits

@PaulWhite9 Exactly, mysql

in general i would assume its working this way : loop over all rows, generate a id and try to insert it into the new table for each row, if a exception occured -> repeat process with new id

But i have no clue how i could translate this to sql... i bet this can not be represented as a single select/insert query

It might be possible but the select part would likely be a UNION ALL query, where one leg would select rows with non-conflicting IDs and the other select and replace the conflicting IDs.

In any event, there are at least two additional separate problems here: 1) how to determine unused ID values so that they can be re-used, and 2) how to re-use them i.e. how to substitute them for the old IDs.

I mean, I would consider those as additional problems if I were to solve this in a set-based manner (which is how I prefer to solve database problems)

This looks a little too much for a quick answer anyway. Seems worth posting as a question on the main, in my opinion.

@AndriyM Alright thanks a lot for your help ^^

Yeah you'd need someone with more than passing familiarity with MySQL

Especially if the version used is < 8.0. Those really require you to have experience handling such issues, given their limited capabilities (language-wise at least).

@genaray Adding a fiddle (dbfiddle.uk, SQL Fiddle, DB Fiddle) with a sample setup goes a long way in questions of this kind. People are more likely to try and help if they have something to work with.