updating sql query value with select statement

Question

I am trying to execute a query which is something like:

update table set column=(select column1 from table1);

I just want to store the value from other table to my column

but when i try my sql query it says

ERROR 1242 (21000): Subquery returns more than 1 row

definitely this means my table1 contains more than 1 row so i want to know that is there any way to store data into column from other table with multiple row.

or basically saving content of other table as a text something like

 update table set column='Data in text from other table';

This is because your subquery (select column1 from table1) returns more than 1 row as the error message says. You can use limit 0,1 to get it working. But if this your expected result, I cannot tell you. — dns_nx
– dns_nx, Commented Mar 4, 2016 at 13:03
I removed the extraneous database tags. Feel free to add the tag for the database you are using. — Gordon Linoff
– Gordon Linoff, Commented Mar 4, 2016 at 13:04

Gordon Linoff · Accepted Answer · 2016-03-04 13:05:12Z

1

You probably need a correlation clause:

update table
    set column = (select column1 from table1 where table.col = table1.col);

You need to decide what column(s) are used for the correlation.

answered Mar 4, 2016 at 13:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user5889646 Over a year ago

i want to add all columns not only one column i want all data from table is this possible

Ankit Agrawal · Accepted Answer · 2016-03-04 13:04:23Z

0

it will work as i am getting your requirement.Please let me know if your requirement is other i ll make changes in query

update table_name t1
inner join table1 t2 on t1.id =t2.id
set column =column1

answered Mar 4, 2016 at 13:04

Ankit Agrawal

2,4541 gold badge17 silver badges28 bronze badges

Comments

moks · Accepted Answer · 2016-03-04 13:16:06Z

0

Your nested query returns multiple rows so you encountered this error. Try in this way

UPDATE FirstTable
SET FirstTable.ColumnName =tbl2.ColumnName 
FROM SecondTable tbl2 WHERE tbl2.Id = FirstTable.Id

There should be a common id or something that will help to find exact row.

answered Mar 4, 2016 at 13:16

moks

995 bronze badges

Collectives™ on Stack Overflow

updating sql query value with select statement

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related