MySQL update query between two tables

Question

I have a user photo utility on my website that is getting updated with some new functionality.

The basic structure is a table with photo data and a table with photo album data.

[userphotos]
pictureid
albumid
userid

[useralbums]
albumid
userid
album_name

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.

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"

So a sample record I have now is as follows…

[userphotos]
pictureid: 100
albumid: 0
userid: 1

[useralbums]
albumid: 1
userid: 1
album_name: "My Photos"

Now what I need to do is update the userphotos table with the new albumid.

I can't get an update statement to run correctly.

It needs to do something like this:

update userphotos set 
userphotos.albumid = useralbums.albumid 
where userphotos.userid = useralbums.userid and 
userphoto.albumid = 0 and 
useralbums.albumname = "My Photos"

Or maybe something easier could be done when I do the initial insert from userphotos to useralbums?

Thanks.

Answer 1

Got it:

UPDATE userphotos p, useralbums a
SET p.albumid = a.albumid
WHERE
a.userid = p.userid
AND a.album_name = "My Photos"
AND p.albumid = 0