Search code examples
mysqlruby-on-railsrubydatabasesphinx

MySQL REPLACE without all fields provided

I have mysql query to replace some records:

REPLACE INTO product_core
(id, title, description, category_id)
VALUES (2, 'new_title', 'new_description', 33)

Can I do the same, but not providing all needed values? Example:

REPLACE INTO product_core
(id, title, description, category_id)
VALUES (2, 'new_title', 'new_description') #no category_id

Got error wrong number of values here near

I want to bulk replace many records, but I do not want to query all fields before. In this example, I want to update category_id for some records, but not for all.

REPLACE INTO product_core
(id, title, description, category_id)
VALUES (2, 'new_title_2', 'new_description_2'), #no category_id
(3, 'new_title_3', 'new_description_3', 34) #with category_id

Is it real to do this? Replace some fields for one record and other fields for second record in one query.

Or if is it real to provide special variable meaning that some fields will be the same as before replace (category_id)?

VALUES (2, 'new_title_2', 'new_description_2', @category_id_same_as_before)
Solution

  • You can't omit these columns from a REPLACE command, unless it is the default value for the column.

    According to the documentation:

    Any missing columns are set to their default values. [...] You cannot refer to values from the current row and use them in the new row.

    It may be better to use a standard UPDATE command instead, which can reference the current column value.