Help with SQL-query finding and updating uid's from non-existing users in comment table

Question

I need help to make a SELECT from the drupal database.

Two or three tables

• comment
• users
• node (maybe)

I want to find all uid's in the comment table from former users but where the uid>0 in the comment table. uid's for former users but whom don't exist in the users table.

I also would like to make an UPDATE-script that can set all uid's from non-existing users in the comment table to zero "0".

This will solve some of the search indexing problem, where it stopps at certain nodes.

Please help me.

Answer 1

I think this should do it. Make sure you take a backup before running this:

UPDATE {comments} SET uid = 0
WHERE uid > 0
AND cid IN
(
  SELECT cid FROM {comments} AS comment
  LEFT JOIN {users} AS user 
  ON user.uid = comment.uid
  WHERE user.uid IS NULL
)