Help with SQL

[ibaker]

I have a table called xf_user_email with just 1 field called email_address containing email addresses and there is about 1,000 rows

What I need is an update query for the xf_user table that has 12,000 rows that will change the usergroup_id of every user whose email address matches one of the email addresses in the xf_user_email table

Your help is greatly appreciated

 

[Joe Kuhn]

Try this first. If you like the results, I'll get back to you after i get to my day job.

select u.*
from xf_user_email e
join xf_user u
on e.email_address = u.?

What's the matching field name in the xf_user table? I could assume it's the same as the field in the other table, but with update statements, you assume nothing.

Also, what do you want to change the usergroup_id to?

Later.

 

[Joe Kuhn]

Here's the update statement if you like the result count and data from the above select statement. Fix the new value for usergroup_id before you run this:

UPDATE xf_user
SET usergroup_id = 'new value'
FROM xf_user u
INNER JOIN xf_user_email e
ON u.email_address = e.email_address

 

[Joe Kuhn]

We always do our update statements with a transaction. Then when we're happy with the row count, we comment out the rollback and comment in the commit.

begin transaction

UPDATE xf_user
SET usergroup_id = 'new value'
FROM xf_user u
INNER JOIN xf_user_email e
ON u.email_address = e.email_address

rollback
--commit

Be safe.

 

[ibaker]

Sorry, I should have provided more info:

Objective: I have found nearly 1,000 users of my site (approx 9%) with an invalid email address and have a table with those invalid email addresses. I want to clean my site up and put those users into an "Invalid Email" primary usergroup. I then want to delete all users in the "Invalid Email" usergroup that have 0 posts (approx 80%). For the remainder I have set permissions that a user in the "Invalid Email" usergroup can not access any forums untill they update their email address and advise. This will also provide a clean user list that I can email, without bounce back and black banning my site emails, an email advising on the latest updates to the site to hopefully get many of the older users coming back to my site.

So, I have a table xf_email_address
It has 1 field called address
It has nearly 1,000 rows of email addresses

I have the standard table for users called xf_user
It has a field called email
It has another field called user_group_id
It has around 12,000 rows of users

I have a usergroup called Invalid Email which has an ID of number 11

I want to change the xf_user.user_group_id to 11 for every user in the xf_user table where xf_user.email = xf_email_address.address

 

[Joe Kuhn]

When you're happy with the row count, comment out the rollback and comment in the commit. I've made the changes you referred to above.

begin transaction

UPDATE xf_user
SET user_group_id = 11
FROM xf_user u
INNER JOIN xf_email_address e
ON u.email = e.address

rollback
--commit

And we always hit the commit twice when we're done to be sure no transactions are left open which will lock things up pretty tight. A second commit should throw an error which is what you want.

 

[ibaker]

Thanks so much for your help @Joe Kuhn

I just simply ran the SQL you created:

begin transaction

UPDATE xf_user
SET user_group_id = 11
FROM xf_user u
INNER JOIN xf_email_address e
ON u.email = e.address

rollback
--commit

on my local test copy and got the following error:

Error

Static analysis:


1 errors were found during analysis.

    Unrecognized keyword. (near "transaction" at position 6)

SQL query:


begin transaction UPDATE xf_user SET user_group_id = 11 FROM xf_user u INNER JOIN xf_email_address e ON u.email = e.address rollback --commit


MySQL said: Documentation


#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'transaction


UPDATE xf_user

SET user_group_id = 11

FROM xf_user u

INNER JOI' at line 1

 

[Joe Kuhn]

Can't tell by the error msg what the problem might be. Remove the transaction and commit lines and try again.

 

[ibaker]

Thanks mate but error again:

Error

SQL query:

UPDATE xf_user
SET user_group_id = 11
FROM xf_user u
INNER JOIN xf_email_address e
ON u.email = e.address

rollback

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM xf_user u
INNER JOIN xf_email_address e
ON u.email = e.address

rollbac' at line 3

 

[Joe Kuhn]

Remove the rollback line

 

[Joe Kuhn]

There it is. Remove the rollback line.

 

[ibaker]

Still throwing an error. Here are the screen shots:
1. The table of fields for email addresses:


2. The table of fields for the users:


3. The SQL I ran:


4. The error I received:

 

[Joe Kuhn]

We've confirmed these two fields exist:

xf_user.email
xf_email_address.address

UPDATE xf_user
SET user_group_id = 11
FROM xf_user u
INNER JOIN xf_email_address e
ON u.email = e.address

I'll have to dial in to my db and see what the problem is. Perhaps the keywords are wrong for your brand of db.

 

[Joe Kuhn]

Table xf_email_address doesn't exist in my xf database.

What version xf are you using? I'm on the latest.

 

[ibaker]

Table xf_email_address doesn't exist in my xf database.

What version xf are you using? I'm on the latest.

That is a table I created and populated it by importing an excel spreadsheet which contained a list of all the current user email addresses that I have found to be invalid.

 

[Joe Kuhn]

That is a table I created and populated it by importing an excel spreadsheet which contained a list of all the current user email addresses that I have found to be invalid.

Ok, let's try this:

UPDATE xf_user
SET user_group_id = 11
FROM xf_user
WHERE email not in (select address from xf_email_address)

 

[ibaker]

Still an error:

Error

SQL query:

UPDATE xf_user
SET user_group_id = 11
FROM xf_user
WHERE email not in 'veryintresting5@gmail.com'

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM xf_user
WHERE email not in 'veryintresting5@gmail.com'' at line 3

However this worked for the one email address:

UPDATE xf_user SET `user_group_id`= 11 WHERE `email`= 'veryintresting5@gmail.com'

 

[Joe Kuhn]

UPDATE xf_user
SET user_group_id = 11
FROM xf_user
WHERE email not in 'veryintresting5@gmail.com'

Change that last line to be:

WHERE email not in (select address from xf_email_address)

 

[Joe Kuhn]

Where did you get: 'veryintresting5@gmail.com' ?

 

[ibaker]

(select address from xf_email_address)

I selected an address in the xf_email_address table