What's wrong with my MySQL statement?

Question

UPDATE table1 SET announcer = ( SELECT memberid
FROM ( table1
JOIN users ON table2.username = table1.announcer
) AS a
WHERE a.username = table1.announcer )

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a where a.username=table1.announcer)' at line 1

Answer 1

Try:

UPDATE announcements a
SET announcer =
(SELECT memberid
FROM users u
WHERE u.username = a.announcer)

Answer 2

You can also do the JOIN in the UPDATE

UPDATE announcements JOIN users
SET announcements.announcer=users.memberid
WHERE announcements.username=users.username;

Note: For safty reasons (until your sure announcers get copied over right) I'd instead create a new column, say announcerNew then

UPDATE announcements JOIN users
SET announcements.announcerNew=users.memberid
WHERE announcements.username=users.username;

Answer 3

You are selecting memberid as only field for the temporary table 'a', then you want to use a non-existing field called 'username' from this table 'a'.

Answer 4

It's hard to say what's wrong with your SQL, because you don't tell us what it's supposed to do.

Assuming that the Table2 is a typo and what you want to do is replace a username with it's corresponding member id (where there is no overlap between memberid and username), then try this:

UPDATE table1 SET announcer = ( SELECT memberid FROM users WHERE users.username = table1.announcer )