CODEWITHSUNDEEP
mysqlsql
user3707642
user3707642

Reputation: 11

MySQL, Inserting data into multiple columns

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?

Upvotes: 1

Views: 380

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

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.

Upvotes: 4

sorav sahu
sorav sahu

Reputation: 51

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

Upvotes: -2

lmove
lmove

Reputation: 317

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

Upvotes: 0

Related Questions