Insert other values when using Insert Ignore

Question

I got my answer from Bill. But how do I insert other default values not found on the loop select?

I'm doing this which works:

SET @createdAt = STR_TO_DATE(NOW(), '%Y-%m-%d %H:%i:%s');

INSERT IGNORE INTO fancy_table (userId, content, createdAt)

-- Values
SELECT
  userId,
  content,
  -- I faked this
  REPLACE(some_random_col, 'sometimes-value-does-not-match', @createdAt) as createdAt
FROM users
WHERE foo = 'bar';

What if I want to update more columns of fancy_table, how to do that? The "fake" REPLACE column is wrong to me.

EDIT:

How can I achieve something like this:

SET @createdAt = STR_TO_DATE(NOW(), '%Y-%m-%d %H:%i:%s');

INSERT IGNORE INTO fancy_table (userId, content, createdAt, etc...)

VALUES (
  (SELECT
    userId,
    content
  FROM users
  WHERE foo = 'bar';),

  @createdAt,

  etc...
)

The accepted answer in the duplicate link contains a link to the documentation where there are examples with SELECT. — forpas, May 13 '21 at 12:46
yes but Im not experience ant duplication here so `ON DUPLICATE KEY UPDATE` would not work in my case. Strange it's marked as duplicate. Ill read the docs instead. — Sylar, May 13 '21 at 12:47
If the duplicate link does not apply to your requirement then edit your question and explain better what you want. As it is currently phrased it looks like you want to insert new rows in the table, but if a row already exists update a column in the existing row. — forpas, May 13 '21 at 12:51
Not sure how to put it. I need to insert default values for specific cols for `fancy_table`. Not sure if that goes before the select or after the select. The select does not contain the value I want for the other columns of `fancy_table` ie `created_at`, `updated_at` etc. — Sylar, May 13 '21 at 12:56

score 1 · Accepted Answer · answered May 13 '21 at 13:04

You can use @createdAt directly in the SELECT statement:

SET @createdAt = STR_TO_DATE(NOW(), '%Y-%m-%d %H:%i:%s');

INSERT IGNORE INTO fancy_table (userId, content, createdAt)
SELECT
  userId,
  content,
  @createdAt
FROM users
WHERE foo = 'bar';

but, @createdAt , is actually the current timestamp which you can get with CURRENT_TIMESTAMP or NOW():

INSERT IGNORE INTO fancy_table (userId, content, createdAt)
SELECT
  userId,
  content,
  CURRENT_TIMESTAMP
FROM users
WHERE foo = 'bar';

I actually thought the columns of both tables should match! You live and learn at the same time. Thanks! — Sylar, May 13 '21 at 13:07

Insert other values when using Insert Ignore

1 Answers1