Update a Single Field without overwriting Nulls on other Fields

I'm updating a single field on a table using the standard Update action that comes with all tables on the data tab. When I do this, any null field in that row is changed to a default value (0, '', or '1900-01-01'). Is there any way to prevent this, i.e. to only update the field I want to update and leave the other fields alone? I'm familiar with the SQL action component, which should work? Is that the best way to accomplish this task, or is there a better way?

Thanks



Is this record on a foreign table or an OutSystems table (entity)? Reason I am asking is that if this is an OS entity, your record when created will be populated with 'default' values. This is because OS have no null values inserting into the database.

The table is on a Microsoft SQL Server database connected to the website through Integration Studio. The table allows Nulls on some fields and I am not allowed to  change those fields.

Hi Kevin,

I don't think there's a way to prevent this with the default generated Update CRUD. If you want this specific behaviour, there's a couple of solutions you can try:

a) you could write an Advanced Query with an UPDATE statement for your specific field with a WHERE clause restricting it the record(s) you want updated. This should leave other Entity fields untouched;

b) I don't have access to an external entity to check, but do you have access to the Update Behaviour property in those external entities? It can be changed to only set fields that have been changed, so it cause the Update CRUD to not mess with your remaining fields. "Supports Nulls" might also be worth a try.

Afonso. 

Thanks for the response. Changing the Update Behavior property sounds like the solution, but I cannot see it on the Entity tab so I don't think I have access to it. There is a Default Value behavior that allows 'Convert to/from NULL' but that did not work. Please let me know if you have any ideas about how I might change Update Behavior.

Just to make sure, do you not have access to these settings in your Entities? Sorry for not being able to check, I don't have an environment with external entities on hand.


If neither of those properties worked out, then I think your other alternative is to write SQL in an Advanced Query to only update the fields you want to change.

Solution

Hi Kevin,

I believe you only have 2 options, use an SQL query, or using a Stored Procedure, but that's probably unnecessarily complicated.

Thing is, in External References, the Update Behaviour is disabled:

In fact, all of the Advances setting are.

Solution

Yeah. I figured SQL query might be my only real option. No problem. I was just looking for the best way to do this. 

Thanks Afonso and Carlos for your responses.