MySQL, Inserting data into multiple columns

Question

I'm trying to insert data from one table into another. I would like to put the info from unit_66 in one column and the info for unit_166 into another column but I'm having trouble using two SELECT statements, I get this error when executing the code:

[Err] 1242 - Subquery returns more than 1 row

CREATE TABLE time_test (unit_66 BIGINT, unit_116 BIGINT);

insert into time_test (unit_66, unit_116)
VALUES 
(
(select time_stamp from `events` where unit_id = 66 LIMIT 50),

(select time_stamp from `events` where unit_id = 116 LIMIT 50)
);

Can anyone tell what the problem it is?

Time_stamp is huge im just limiting it to 50 rows until i can get it to work. — user3707642
– user3707642, Commented Jun 8, 2014 at 19:25
That is your problem, the way it is written, it expects one value, not 50 rows of values. — Devon Bessemer
– Devon Bessemer, Commented Jun 8, 2014 at 19:25

Gordon Linoff · Accepted Answer · 2014-06-08 19:48:57Z

4

I think you want insert . . . select. You are using a scalar subquery, but having it return up to 50 values. That is causing your error. If you had a limit 1 in each subquery, then it would work.

It is a little hard to guess what you are trying to do, but let me try:

insert into time_test(unit_66, unit_116)
    select e66.time_stamp, e116.timestamp
    from events e66 join
         events e116
         on e66.unit_id = 66 and e116.unit_id = 116
    order by rand()
    limit 50;

This is just a guess, because the intention of your query is not obvious to me.

edited Jun 8, 2014 at 19:48

answered Jun 8, 2014 at 19:28

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

4 Comments

S. Miller Over a year ago

Why are you doing an order by rand()?

Gordon Linoff Over a year ago

There's no order by in the original query. This will randomize the order explicitly. More importantly, it will make it less likely that the same value from e66 will appear multiple times in the results.

user1613360 Over a year ago

@GordonLinoff The more proper way is to loop a procedure which inserts the records with count of number of rows in the events table as looping limit.

user3707642 Over a year ago

I'm new to MySQL and im not 100% sure how this code works but when i try compiling it, gives me this error.[Err] 1052 - Column 'time_stamp' in field list is ambiguous

lmove · Accepted Answer · 2014-06-08 19:32:02Z

0

I suppose your time_test table is not normalized. So, you can use INSERT INTO SELECT statement. With this solution you will copy 100 records from events table.

INSERT INTO time_test (unit_66)
SELECT time_stamp
FROM events
WHERE unit_id = 66 LIMIT 50

INSERT INTO time_test (unit_116)
SELECT time_stamp
FROM events
WHERE unit_id = 116 LIMIT 50

answered Jun 8, 2014 at 19:32

lmove

3273 silver badges11 bronze badges

1 Comment

Devon Bessemer Over a year ago

That will insert two different rows, one with a blank unit_116 and one with a blank unit_66.

sorav sahu · Accepted Answer · 2014-06-08 19:33:13Z

-2

insert into time_test (&unit_66, &unit_116) VALUES ( (select time_stamp from events where unit_id = 66 LIMIT 50), (select time_stamp from events where unit_id = 116 LIMIT 50) ); just try this

answered Jun 8, 2014 at 19:33

sorav sahu

517 bronze badges

Collectives™ on Stack Overflow

MySQL, Inserting data into multiple columns

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related