将一列从一个表复制到另一个表

时间:2022-09-21 15:13:09

I am confused about how to copy a column from one table to another table using where. I wrote SQL query but it says transaction lock time exceeded or query returns more than one row.
using mysql
Basically,
I have:

我很困惑如何使用where将列从一个表复制到另一个表。我写了SQL查询,但它说超出事务锁定时间或查询返回多行。使用mysql基本上,我有:

Table 1:  Results
BuildID  platform_to_insert

Table 2:  build
BuildID correct_platform

update results set results.platform_to_insert 
     = (select correct_platform from  
       build where results.BuildID = build.BuildID)

2 个解决方案

#1


70  

I do not believe you need a sub query.

我不相信你需要一个子查询。

UPDATE results, build
SET    results.platform_to_insert = build.correct_platform
WHERE  results.BuildID = build.BuildID

#2


5  

There are two options here:

这里有两个选项:

  1. update your tables to use BuildID as a primary key (to avoid duplicates)
  2. 更新表以使用BuildID作为主键(以避免重复)
  3. update your subquery to only return one result

    更新子查询只返回一个结果

    UPDATE results SET results.platform_to_insert = (
        SELECT correct_platform
        FROM build
        WHERE results.BuildID=build.BuildID LIMIT 1
    );
    

#1


70  

I do not believe you need a sub query.

我不相信你需要一个子查询。

UPDATE results, build
SET    results.platform_to_insert = build.correct_platform
WHERE  results.BuildID = build.BuildID

#2


5  

There are two options here:

这里有两个选项:

  1. update your tables to use BuildID as a primary key (to avoid duplicates)
  2. 更新表以使用BuildID作为主键(以避免重复)
  3. update your subquery to only return one result

    更新子查询只返回一个结果

    UPDATE results SET results.platform_to_insert = (
        SELECT correct_platform
        FROM build
        WHERE results.BuildID=build.BuildID LIMIT 1
    );