title: MySQL update query between two tables
body: <p>I have a user photo utility on my website that is getting updated with some new functionality.</p>
  
  <p>The basic structure is a table with photo data and a table with photo album data.</p>
  
  <pre><code>[userphotos]
  pictureid
  albumid
  userid
  
  [useralbums]
  albumid
  userid
  album_name
  </code></pre>
  
  <p>Every user that uploads a photo gets a default album that has an albumid = 0 in userphotos and this default album has no record in useralbums.</p>
  
  <p>This is changing so I inserted a record for each distinct 0 albumid and userid from userphotos into useralbums where the albumid is an auto increment field and I defaulted the albumname to "My Photos"</p>
  
  <p>So a sample record I have now is as follows…</p>
  
  <pre><code>[userphotos]
  pictureid: 100
  albumid: 0
  userid: 1
  
  [useralbums]
  albumid: 1
  userid: 1
  album_name: "My Photos"
  </code></pre>
  
  <p>Now what I need to do is update the userphotos table with the new albumid.</p>
  
  <p>I can't get an update statement to run correctly.</p>
  
  <p>It needs to do something like this:</p>
  
  <pre><code>update userphotos set 
  userphotos.albumid = useralbums.albumid 
  where userphotos.userid = useralbums.userid and 
  userphoto.albumid = 0 and 
  useralbums.albumname = "My Photos"
  </code></pre>
  
  <p>Or maybe something easier could be done when I do the initial insert from userphotos to useralbums?</p>
  
  <p>Thanks.</p>