copy one column from one table to another

Question

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)

How many rows do you have in each table? Show us the complete and actual error message that MySQL gives you — Jan Hančič, Jan 06 '10 at 19:23
the way you have written your query can indeed return more than one row from the subselect: which value of correct_platform should be taken if more than one exists for a given buildID? — davek, Jan 06 '10 at 19:24
what I want to do is : for each BuildID from results, compare it with BuildID in build is same, then take that correct_platform from build and insertinto platform_to_insert in Results — JPro, Jan 06 '10 at 19:32
why doesnt this work? update results R set R.correct_platform_in_results_table = B.correct_platform inner join build B on R.BuildID=B.BuildID — JPro, Jan 06 '10 at 19:50

score 73 · Accepted Answer · answered Jan 06 '10 at 21:39

73

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

answered Jan 06 '10 at 21:39

Benoît Vidis

3,908
2
23
24

1

Wow, awesome answer. This fundamentally changes my understanding of relational databases – Mansiemans Mar 20 '14 at 14:38
Hii i have tried this query but not working properly pls check : UPDATE staff_login_times, staff_rotas SET staff_login_times.scheduled_start = staff_rotas.startdate WHERE staff_login_times.actual_start = staff_rotas.startdate; – HIR Nov 30 '16 at 11:32

score 5 · Answer 2 · edited Mar 06 '13 at 19:56

5

There are two options here:

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

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

edited Mar 06 '13 at 19:56

insertusernamehere

23,204
9
87
126

answered Jan 06 '10 at 19:42

tmpvar

1,331
1
8
12

the problem is that the WHERE clause is matching multiple elements in the subquery. This can be solved by limiting the subquery's result set to one or making *.BuildID a primary key to avoid duplicates in the first place. In either case all of the rows will be updated. – tmpvar Jan 06 '10 at 20:14
I have executed query as per your suggestion but query is not executing will you please help me my post is in https://stackoverflow.com/questions/59064755/copy-column-from-one-table-to-another-table-but-hangs-db-browser-for-sqlite – Siddhpura Amit Nov 27 '19 at 07:15

copy one column from one table to another

2 Answers2

Linked