Multiple Record Update

[Corey McGlone]

Quick SQL question for the experts out there...

I'm working on a database that has two tables that I'd like to combine into one. Here's the basic structure:

Table: Users
 
ID          Role
--------------------
abc01       1
abc02       2
abc03       1
abc04       3
abc05       3
 
Table: UserNames
 
ID          Name
-------------------------------
abc01       John Doe
abc02       Bertrand Blass
abc03       Joe Futz
abc04       Mary Contrary
abc05       Jenny Jones
 

My goal is to just copy the name column from the UserNames table into the Users table, so that I end up with this:

Table: Users
 
ID          Role          Name
-----------------------------------------------
abc01       1             John Doe
abc02       2             Bertrand Blass
abc03       1             Joe Futz
abc04       3             Mary Contrary
abc05       3             Jenny Jones

I've already created the Name column within the Users table, but I'm unsure of how to write the SQL query to copy the names over from UserNames to Users. I want to do something like this:

UPDATE Users SET Name = (SELECT Name FROM UserNames)
WHERE Users.ID = UserNames.ID

I just need to know what the correct syntax is.

Thanks, folks.

[Muhammad Saifuddin]

The correct syntax is.

UPDATE Users SET name = (SELECT Names FROM UserNames WHERE Users.ID = UserNames.ID)

[Corey McGlone]

Originally posted by Saif uddin:
The correct syntax is.

UPDATE Users SET name = (SELECT Names FROM UserNames WHERE Users.ID = UserNames.ID)

Thank you. Worked like a charm.