CODEWITHSUNDEEP
mysqlsql-update
JPro
JPro

Reputation: 6546

copy one column from one table to another

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: 

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)

Upvotes: 28

Views: 37714

Answers (2)

tmpvar
tmpvar

Reputation: 1341

There are two options here:

  1. update your tables to use BuildID as a primary key (to avoid duplicates)

  2. 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
);

Upvotes: 5

Benoît Vidis
Benoît Vidis

Reputation: 3902

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

Upvotes: 73

Related Questions