Possible to clear a field in a bulk Upsert?

[rafeyfanwar]

I've generated the go files for my project using the --feature sql/upsert flag and it works for most of what i'm trying to do, but i haven't been able to figure out if/how i can set a field to NULL in an upsert.

I initially tried using the SetNillable setters, which i realized ignore nil values rather than clearing that column in that row. I see that there are ClearX() functions that can be set on Updates, but since upserts use Creates i don't have access to those functions.

Is there any way i can clear nil fields on an upsert?

[rafeyfanwar]

Just noticed a similar question was asked here before: #1979

I see a solution for always setting a field to NULL on upsert, but not for both setting it to a value for some rows and clearing it for other rows all as a part of the same upsert.

[a8m]

Hey @rafeyfanwar 👋

but not for both setting it to a value for some rows and clearing it for other rows all as a part of the same upsert.

Can you elaborate, please? What do you expect the Ent API and the underlying SQL statement to look like?

[rafeyfanwar]

Hey @a8m! Sure thing. Here's an example of what i'm trying to achieve. Let's say i start out with the following table:

mytable
id is the primary key
+------+--------+---------+
| id   | name   | email   |
+------+--------+---------+
+------+--------+---------+
| id_1 | name_1 | email_1 |
+------+--------+---------+
| id_2 | name_2 | email_2 |
+------+--------+---------+

I want to bulk upsert such that I can set the email field for row with id=id_1 to a new value, but clear it for row with id=id_2. The ENT code would look something like this:

var modelsToUpsert []*ent.MyDataModelCreate
	
builderOne := client.MyDataModel.Create().
	SetID("id_1").
	SetNillableName("name_1_new").
	SetNillableEmail("email_1_new")

modelsToUpsert = append(modelsToUpsert, builderOne)

builderTwo := client.MyDataModel.Create().
	SetID("id_2").
	SetNillableName("name_2_new")
	// Clear existing email for this row
	// .ClearEmail() is not an option since this is a Create
	// .SetNillableEmail(nil) leaves the column as-is

modelsToUpsert = append(modelsToUpsert, builderTwo)

err := client.
MyDataModel.
CreateBulk(modelsToUpsert...).
OnConflict().
UpdateNewValues().
// If i add a custom Update() function here for clearing email, it will apply to all lines when i only want to clear certain lines.
Exec(ctx)

I want the result of this operation to be for the table to be updated to this:

mytable
+------+------------+-------------+
| id   | name       | email       |
+------+------------+-------------+
+------+------------+-------------+
| id_1 | name_1_new | email_1_new |
+------+------------+-------------+
| id_2 | name_2_new | NULL        |
+------+------------+-------------+

I'm admittedly not familiar with SQL in this much detail, but i believe the intended underlying SQL statement would be something like this:

INSERT INTO mytable (id, name, email)
    VALUES
    ('id_1', 'name_1_new', 'email_1_new'),
    ('id_2', 'name_2_new', NULL),
    AS new
ON DUPLICATE KEY UPDATE
    name = new.name
    email = new.email

This example is simplification btw -- the real logic loops through many lines and requires multiple columns to be updated in this way (potentially to a new value, potentially to NULL). Let me know if i can elaborate/clarify further, thanks!

[a8m]

Hey @rafeyfanwar,
This actually works as expected; try to run the operation with client.Debug() and you'll see the output.

client.T.CreateBulk(
	client.T.Create().SetID(10),			// Will set "email" to "<nil>" (NULL).
	client.T.Create().SetID(20).SetEmail("X"),	// Will set "email" to "X".
).
OnConflict().
UpdateNewValues().
ExecX(ctx)

// query: INSERT INTO `t` (`id`, `key`) VALUES (?, ?), (?, ?) ON DUPLICATE KEY UPDATE `id` = `t`.`id`, `key` = VALUES(`key`)
// args: [10, <nil>, 20, "X"]

One thing though, is that current statements pass <nil> arguments instead of setting the value to NULL in mixed cases like this, but this has no effect on the correctness of this statement.

[rafeyfanwar]

@a8m thanks for the additional context!

current statements pass <nil> arguments instead of setting the value to NULL

Is there a way to modify your code snippet above such that it will set an existing email value to NULL? Passing in the <nil> argument, while resulting in a correct statement, does not accomplish what i'm hoping to do since it does not overwrite the old value of the email column to NULL.

[a8m]

Are you able to reproduce this? Because I'm not. Please, see #2849.

[a8m]

I decided to inline NULLs instead of passing them as arguments. See #2849

Closing, but please feel free to reopen it if you still have issues with it. Also, you're more than welcome to join our Discord community 😎