从另一个表插入并插入新值

时间：2021-05-14 15:41:38

I have two tables, one of them is 'user_flag' and the other is 'playlist_data'.

我有两个表，其中一个是'user_flag'，另一个是'playlist_data'。

I want to take all 'object_id' column entries of 'user_flag' and place them into the respective 'object_id' column of 'playlist_data', but only if those entries have '3' as the 'user' entry, and that they do not already exist (no duplicate 'object_id's!).

我想获取'user_flag'的所有'object_id'列条目并将它们放入'playlist_data'的相应'object_id'列中，但前提是这些条目的'3'作为'user'条目，并且它们不是已经存在（没有重复的'object_id'！）。

I tried to learn how to do it and this is what I found:

我试着学习如何做到这一点，这就是我发现的：

INSERT INTO playlist_data (object_id)
SELECT object_id FROM user_flag
WHERE user='3';
ON DUPLICATE KEY UPDATE object_id=object_id

Will this work properly?

这会正常吗？

But I'm also trying to do more at the same time, and I can't seem to find an answer:

但我也试图在同一时间做更多事情，我似乎无法找到答案：

1) I want to also insert new data with this. I want all of the newly inserted entries to also contain '5' in the 'filetype' column of 'playlist_data'.

1）我还想用这个插入新数据。我希望所有新插入的条目在'playlist_data'的'filetype'列中也包含'5'。

Do I just

我只是

INSERT INTO playlist_data (filtype)
VALUES (5)

in the middle of all of this?

在这一切的中间？

2) Both tables also have an 'id' column, will it automatically generate a new id followed from the latest 'id' of 'playlist_data'?

2）两个表都有一个'id'列，它会自动生成一个新的id，跟随'playlist_data'的最新'id'吗？

As in for example, I'm transferring from 'user_flag' an entry with the 'id' of '150', while the highest 'id' in 'playlist_data' is '63', will the inserted one by '64', or do I need to define that somehow?

例如，我从'user_flag'转移'id'为'150'的条目，而'playlist_data'中的最高'id'为'63'，将插入的'id'为'64'，或者我需要以某种方式定义吗？

1 个解决方案

#1

0  

Just add the value in the SELECT:

只需在SELECT中添加值：

INSERT INTO playlist_data (object_id, filtype)
    SELECT object_id, 5
    FROM user_flag
    WHERE user = 3
    ON DUPLICATE KEY UPDATE object_id = VALUES(object_id);

Notes:

笔记：

  • I am guessing that the id columns are numbers. Hence, I removed the single quotes.
  • 我猜id列是数字。因此，我删除了单引号。
  • I use VALUES(object_id) in the ON DUPLICATE KEY UPDATE.
  • 我在ON DUPLICATE KEY UPDATE中使用VALUES（object_id）。
  • It is important to have the semicolon only at the very end of the statement.
  • 分号只在语句的最后才是重要的。

#1

0  

Just add the value in the SELECT:

只需在SELECT中添加值：

INSERT INTO playlist_data (object_id, filtype)
    SELECT object_id, 5
    FROM user_flag
    WHERE user = 3
    ON DUPLICATE KEY UPDATE object_id = VALUES(object_id);

Notes:

笔记：

  • I am guessing that the id columns are numbers. Hence, I removed the single quotes.
  • 我猜id列是数字。因此，我删除了单引号。
  • I use VALUES(object_id) in the ON DUPLICATE KEY UPDATE.
  • 我在ON DUPLICATE KEY UPDATE中使用VALUES（object_id）。
  • It is important to have the semicolon only at the very end of the statement.
  • 分号只在语句的最后才是重要的。
标签：

相关文章