How to insert data with subquery using SELECT * as VALUE?

Question

I have 2 queries. First query is

SELECT date 
FROM tableA 
WHERE 
    db='mydb' AND 
    date > (SELECT MAX(date) FROM tableB WHERE db='mydb') 
LIMIT 1;

Second query is

INSERT INTO tableB (date, db, comment) 
SELECT * FROM tableA 
WHERE 
    db = 'mydb' AND 
    date = (date that i got from first query);

Data types are: db(VARCHAR), date(date), comment(mediumblob).

statement SELECT * FROM tableA WHERE db = 'mydb' AND date = (date that i got from first query); returns exactly 1 row, but somehow second query returns that 1242 - Subquery returns more than 1 row.

I have tried to change SELECT * statement to each columns, but it didn't work on column comment. And it is impossible to do this on trigger because tableA is in system table.

in first query you are aggregating, so you get 1 row, there can be multiple records matching for the same date, so you might get multiple records in your second query. — PSK, Feb 20 '20 at 04:50
Are you referring to [this](https://stackoverflow.com/questions/9422529/mysql-how-do-you-insert-into-a-table-with-a-select-subquery-returning-multiple-r/9422561)? — Kyle_397, Feb 20 '20 at 04:51
@GROVER. I was trying to clone mysql query log to my database. the clause ```db = 'mydb'``` is to filter that mysql just take queries on my database — hamzbond, Feb 20 '20 at 06:39
@PSK no, the dates are all unique. date format is Y-m-d H:i:s and inserted automatically by mysql. I'm sure the dates are unique — hamzbond, Feb 20 '20 at 06:41
@Kyle_697 I have read that before. but i don't think it solve my problem — hamzbond, Feb 20 '20 at 06:41

score 0 · Answer 1 · answered Feb 20 '20 at 05:29

0

As far as I understand the goal, you are trying to copy all fresh values from A to B. Here "fresh" means "date not exists in the target table yet".
I think it can be simplified to this query:

INSERT INTO `tableB` (`date`, `db`, `comment`) 
SELECT `date`, `db`, `comment` 
FROM `tableA` 
WHERE `db` = 'mydb' 
AND `date` > (SELECT MAX(`date`) FROM `tableB` WHERE `db` = 'mydb')

answered Feb 20 '20 at 05:29

artoodetoo

918
10
55

Thanks @artoodeeto, I use this query in firs time i build the program. But it returns error when i insert column `comment`. the data type is mediumblob, filled with multiple ```"``` and it crashes the query unless i use ```*``` – hamzbond Feb 20 '20 at 06:44
Please don't use * here. Use exact field list and the same field order on both insert and select clauses. It should not crash if you have compatible data types on fields. – artoodetoo Feb 20 '20 at 13:32

How to insert data with subquery using SELECT * as VALUE?

1 Answers1