Help with SQL

[Joe Kuhn]

I don't understand why you changed that last line in:

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

 

[Joe Kuhn]

if that's the only address in xf_email_address, then this will do what you said you want:

UPDATE xf_user
SET user_group_id = 11
FROM xf_user
WHERE email <> 'veryintresting5@gmail.com'

 

[ibaker]

No, there are almost 1,000 addresses in the xf_email_address table.

aaarrrgh, still error

Error

SQL query: Documentation

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

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 (select address from xf_email_address)' at line 3

 

[Joe Kuhn]

I don't know.

 

[Joe Kuhn]

MariaDB/MySql needs to be clearer. Perhaps you can find someone with experience there.

 

[ibaker]

I have started a conversation and I want to publicly say how appreciative I am for your help which I am sure must be very frustrating for you

 

[Joe Kuhn]

I have started a conversation and I want to publicly say how appreciative I am for your help which I am sure must be very frustrating for you

Heh, patience is a virtue that never ends and we all get to explore that from time to time.

I took the first table you gave me and tried to load it into Sql Server here, which is what I have on my laptop and what I work in daily, but the first three lines of the program threw errors via syntax differences. That's what we're dealing with here.

I could load your table into my xf database, but I'd rather not for a couple of reasons. Perhaps I best bow out and leave it to the MySql experts.

 

[ibaker]

YAY, I did it in my local test db with:

UPDATE xf_user t1
  JOIN xf_email_address t2
    ON t1.email = t2.address
SET t1.user_group_id = 11;

Thanks for all your help and patience @Joe Kuhn

 

[Joe Kuhn]

Look at that. I like the set statement at the end. I'll remember this one. Way to go.

 

[Joe Kuhn]

You been reading the manual?

 

[ibaker]

I previously tried good ol Google for hours but with no luck so I started manually doing it with user search one by one but after a few days of painstakingly clicking the mouse button I tried here. I thought I would give Google one more try and after several attempts of what was suggested here and there I found it

 

[Joe Kuhn]

I bet my problem is the from clause - should have left it out.

 

[Joe Kuhn]

Back at work today, I confirmed the MySql syntax does not work for Sql Server.