MySQL update request between two tables

Question

I have a custom photo utility on my website that is updated with some new features.

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

[userphotos]
pictureid
albumid
userid

[useralbums]
albumid
userid
album_name

Each user uploading a photo receives a default album, which has an album album = 0 in userphotos, and this album by default does not have an entry in user albums.

This is changing, so I inserted the record for each individual 0 album and userid from userphotos into user albums, where the album is an auto-enlargement field, and by default I named the album name “My Photos”

So, I now have a sample record ...

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

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

Now I need to update the userphotos table with a new album.

.

- :

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

- , userphotos ?

.

Answer 1

:

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