only insert data into mysql database of value doesnt exist yet

Question

I have a query that retrieves data every day for a lot of different posts. Before inserting that for each post, I want to check if the data is not the same as the day before for that post.

Table post_metrics:

| id  | date       | value1 | value2 | etc...
| 123 | 2017-01-10 | 20     | 50     | more columns
| 123 | 2017-01-11 | 40     | 80     | more columns
| 124 | 2017-01-10 | 100    | 600    | more columns
| 124 | 2017-01-11 | 410    | 800    | more columns

So lets say the system wants to insert data for 2017-01-12 for post with id 123 It needs to check if value1 is not the same as the day before (40)

I found this stack answer which I think got me in the right direction: MySQL: Insert record if not exists in table in which this is suggested (rewrote it to fit my example table):

INSERT INTO `post_metrics` (`id`, `date`, `value1`, `value2`)
SELECT * FROM (SELECT 123, now() 113, 84344, now()) AS tmp
WHERE NOT EXISTS (
    SELECT `id`, `value1` FROM `post_metrics` WHERE `id` = 10156753760478438 AND `value1` = 84344
)

Now this works great and does the job if I didnt find this problem:

My actual Query:

 INSERT INTO `post metrics minutes` ( `date updated`, `impressions`, `reach`, `fan reach`, `viral reach`, `consumptions`, `consumers`, `engaged users`, `engaged fans`, `engagements`, `storytellers`, `negative feedbacks`, `video views`, `hours video view time`, `video length (sec)`, `video avg time watched`, `content quality`, `type alignment`, `neg.fdbck.rate unweighted`, `eng.rate unweighted`, `video completion rate unweighted`, `post id`) 
 SELECT * FROM ( 
  SELECT now(), 94997, 61475, 87611, 5382, 2677, 1818, 2052, 1890, 577, 540, 53, 21955, 0, 959.13, 0, 'weak', 'normal rates', 0.00086213908092721, 0.0093859292395283, 0, 123) AS tmp
 WHERE NOT EXISTS (
  SELECT `post id`, `impressions` FROM `post metrics minutes` WHERE `post id` = 123 AND `impressions` = 94997)

This returns the error:

Duplicate column name '0'

which comes from the multiple values zero I try to select on row 3. How could I fix this?

`'now()'` - `NOW()` is a function, why are you using it as a string literal? Seems like a column shift issue here. Edit: Seeing your edit, this was based on your original post https://stackoverflow.com/revisions/46346437/1 - are we going to be editing on the fly? — Funk Forty Niner, Sep 21 '17 at 14:26
That is indeed a good point but not the problem ;) (fixed it in the question) — Merijndk, Sep 21 '17 at 14:29
Yeah, It was a new thing for me aswell. Take a look at the question and answer I included in my post if you would like to grasp it! https://stackoverflow.com/questions/3164505/mysql-insert-record-if-not-exists-in-table — Merijndk, Sep 21 '17 at 14:34

score 2 · Accepted Answer · answered Sep 21 '17 at 15:10

Now that you create an alias tmp from the subquery, the column names are by default derived from the values if no names are assigned. If you happen to have same values multiple times the derived column names will be the same.

All you need to do, is to name the columns in the subquery (also the code becomes bit more readable):

INSERT INTO `post metrics minutes` ( `date updated`, `impressions`, `reach`, `fan reach`, `viral reach`, `consumptions`, `consumers`, `engaged users`, `engaged fans`, `engagements`, `storytellers`, `negative feedbacks`, `video views`, `hours video view time`, `video length (sec)`, `video avg time watched`, `content quality`, `type alignment`, `neg.fdbck.rate unweighted`, `eng.rate unweighted`, `video completion rate unweighted`, `post id`) 
SELECT * FROM ( 
  SELECT 
    now() as 'date updated', 
    94997 as 'impressions', 
    61475 as 'reach', 
    87611 as 'fan reach', 
    5382 as 'viral reach', 
    2677 as 'consumptions', 
    1818 as 'consumers', 
    2052 as 'engaged users', 
    1890 as 'engaged fans', 
    577 as 'engagements', 
    540 as 'storytellers', 
    53 as 'negative feedbacks', 
    21955 as 'video views', 
    0 as 'hours video view time', 
    959.13 as 'video length (sec)', 
    0 as 'video avg time watched', 
    'weak' as 'content quality', 
    'normal rates' as 'type alignment', 
    0.00086213908092721 as 'neg.fdbck.rate unweighted', 
    0.0093859292395283 as 'eng.rate unweighted', 
    0 as 'video completion rate unweighted', 
    123 as'post id'
) AS tmp
WHERE NOT EXISTS (
 SELECT *
 FROM `post metrics minutes` 
 WHERE `post id` = 123 AND `impressions` = 94997
)

only insert data into mysql database of value doesnt exist yet

1 Answers1