MySQL Update is Setting the Selected Column in other Rows to Zero

Question

I'm looking to set a column in a selected row in one table (requester.rqgroup) to the value of a selected column in another table (groups.grpnum), one update at a time.

When I run this query:

UPDATE requesters SET rqgroup = (SELECT grpnum from groups WHERE groupname like 'Hosting Open Sys Engineering') AND (WHERE requesters.name LIKE 'J. Smith')

the rqgroup value is set correctly for the row where J. Smith is the name, but the rqgroup value for all the other rows is set to zero.

Suggestions please? Happy New Year, Jonah Giacalone

Answer 1

You don't need to type where twice, that's what's making this weird thing happen.

Run this instead:

UPDATE requesters SET rqgroup = (SELECT grpnum from groups WHERE groupname like 'Hosting Open Sys Engineering') WHERE  requesters.name LIKE 'J. Smith')

Also, since your like statement is being used to find a result with the exact input, instead of like, simply use =

UPDATE requesters SET rqgroup = (SELECT grpnum from groups WHERE groupname = 'Hosting Open Sys Engineering') WHERE  requesters.name = 'J. Smith')

It's faster. (I think)