I need to do something that should be easy: deleting information from the table (I'm deleting adress information that is no longer accurate).
However, I can't seem to do it.
I'm using a data update component and for each field that need to be deleted, I'm setting the source to ''. At runtime, this ends with a sql error.
Does anyone know what should be the proper process to delete the value of a field ?
Thanks for your help
It is more probably a question of integrity constraint, or value type mismatch.In SQL queries you can use NULL values; if you want to do it with Update activity, please check the Advanced parameters checkbox. And please share the exact SQL error
Please try the below sql. Modify the sql with your table name and column name. All the values of the given column in the table will be deleted.
Thanks for your inputs, they helped me solved my problème.
My 2 mistakes were the following:
- updating a value with the '' string (empty string) does not work (the value is not erased)
- when using the NULL value, you canot just enter NULL in the list of field to be updated. You need to click on "Edit the source expression", then enter NULL and validate.
I know the sql way of doing it. Sorry, I am not aware of any other way.
I was looking for a non-sql solution, to keep the workflow easy to understand for people with less database skills.
Or does this mean it is not possible delete some values using the Update data component ?
Yes, I'm doing the "Update" operation because I need to keep the record and just delete some of its value
What operation are you performing with the update data activity?
There is a dedicated "delete" operation in this activity which can be used. From what you described, I understood that you are using the "Update" operation.