You can use the community commons clone action for that. You do need to recreate the objects like Austin is saying. But with using the (deep)clone it should be not that hard.
It might be easier to recreate all of your data instead of trying to add a specialization to the existing records. If your requirements allow it, you could retrieve all the records from the project table, iterate it, and use exclusive splits to determine what specialization to create. Then delete your old records.
Is there anything that you are trying preserve by changing the existing records? Owner, created date, autonumber attributes?
Edit: I was playing around with a database of a test project and I was able to accomplish what you want to do. I don't recommend doing this, but to answer your question on how to create a specialization from an existing generalization, this is how you would do it.
How inheritance is handled in the database is explained by this question.
I created a domain model that looks like this
I first took the same approach you did by running a script in pg admin and manually creating the specialization record and assigning the same id of the corresponding base entity that I wanted to create this specialization record for. Then I ran an update script on the base entity and changed the submetaobjectname to be of type of the specialization. I ran into the same error as you did
The current id belongs to another object type than the given type to set. Type of this MendixObject is 'MyFirstModule.Water', the given id is of type 'MyFirstModule.Project'
This made me think that the id's that are being assigned are associated with the the Mendix object type. So in order to add a new specialization record to an existing base entity, It would need an id of the same type as the specialization.
I started by creating a record for Project (this will be my existing record), then I created a record for water (the new specialization record). Both were created in the Mendix app.
In pgAdmin I looked at the project and water table to verify the two records were there.
In the project table we need to update the id and submetaobject name of the record that is of type project with the same id as the object that is of type water. Also before we do that, we need to delete the record that is of type water in the project table first, or we will run into a unique constraint violation for id.
Just want to double check my tables in pgAdmin before I check the Mendix app.
There is now 1 record in the project table.
Now when I go check the mendix app. The two datagrids will reflect the same.
I also verified that you can open a pop up page with that either record and it won't throw an error.
@Austin, Thanks for you elaborate answer. Was able to follow your instructions. Very useful.
I end up with the same project and projectWater nicely, albeit with a changed value for ID. Since I also have to change the associations, it will take one more SQL for each association, not difficult but a mistake is easily made.
Additional problem is that the databases are in Mendix's V3-cloud.
@Ronald, yet to try the Clone results.
There is a less difficult, but less perfect solution: making it a regular 1-1 related entity. With the spent time already overshot combined with the customer pressing for result and tampering with our core-entity project's id requires too much testing, I chickened out. Hoping to use this gained knowledge in future attempts to improve our project.