SQL Statement request for albums problems with deleted users

[alfisti.net]

Hi,

we deleted users and the existing albums are still online which is fine.
But the userID should set to eg "100" as universal user for all deleted userid.

My skills in MySQL are not so good, so I would like to ask if somebody can help me with the following:

UPDATE album,user SET album.userid = 100 WHERE user.userid ...

The questions is how to request that the userID is not existing. I found some ways in the documentation (for example NOT IN, EXISTS), but I am not able to get them run correctly.

Thanks
André

[BirdOPrey5]

We don't ever recommend manually editing the database and should you attempt to do so always make a backup of the database first in case something goes wrong.

And I have never tried this myself but my guess the best way would be to use a sub-query as described here: http://stackoverflow.com/questions/1...l-not-in-query

My guess would be to try:

UPDATE album SET album.userid = 100 WHERE album.userid NOT IN (SELECT user.userid FROM user)

Again, backup before trying and use at your own risk.

[alfisti.net]

Thank you very much Joe!
Of course I will backup before I try this.

We don't ever recommend manually editing

Of course, but the 3.8 do not delete albums of a user if you delete a user in the backend. This "feature" show albums which are not available anymore, so there are only two ways to fix this issue.
Delete the albums (manually, what you do not recommend) or give them a universal userID (manually, what you do not recommend)

I will try it later and give a feedback if somebody want to change it also.

[BirdOPrey5]

I haven't had the issue personally but in the front end as Admin you can go to "Edit Album" for an album and delete it from VB itself- that would be the supported method.

Good luck.

[alfisti.net]

Thank you very much Joe! Your query work fine

The supported method will only work before you delete a user. Which make it very difficult if you delete hundreds of users by e.g. "last login date".
If you go in the frontend to /forum/album.php?albumid=xx of a deleted user you will get an error that the user do not exist. Thats why I asked for the help.