UPDATE SELECT query?

Question

I want to update a row in my table.

UPDATE content SET title = $title, content = $content, email = ???

The email is the problem, I have the ID of the email address from the users table but not the address, I want to insert the address.

How can I get the email address from the users table in the same UPDATE query?

Users:

id|email
 1 johnsmith@gmail.com

Here's how my query looks for an insert:

INSERT INTO content (title, content, email) SELECT $title, $content, email FROM users WHERE id = $id.

Please note, i'm aware vars need escaping/security etc posted the above for a clear example.

Answer 1

How do you know which users record to use?

Assuming you have a user_id field in the content table then you can do a JOIN on the UPDATE

UPDATE content INNER JOIN users ON content.user_id = users.id 
SET content.title = $title, 
content.content = $content,
content.email = users.email

Answer 2

Why duplicate the data in the content table? Just use a join to get the email address on display:

SELECT c.title, c.content, c.author, u.email 
FROM content c 
LEFT JOIN users u 
    ON c.author = u.id 
WHERE c.id = 1

Answer 3

Simply use like this,

UPDATE content SET title = $title, content = $content,
email = (SELECT email FROM users WHERE id = $id)