两个表之间的 MySQL 更新查询

MySQL update query between two tables

提问by Tom

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.

每个上传照片的用户都会获得一个默认相册，在 userphotos 中的相册 ID = 0，并且该默认相册在 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"

这正在发生变化，因此我将 userphotos 中每个不同的 0 相册 ID 和用户 ID 的记录插入到用户相册中，其中相册 ID 是一个自动增量字段，并且我将相册名称默认为“我的照片”

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.

现在我需要做的是用新的相册更新 userphotos 表。

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.

谢谢。

回答by Tom

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