Design question - update + insert versus delete + insert

[Shweta Parekh]

Hi,

I have a design dilemma here.
I have a 2D array coming from my front end.
array[0][0] = "abc"
....
array[0][2] = "bcd"
....
array[5][0] = "xyz"
....
array[5][2] = "xxx"

My database table workSpec structure looks like this (there is no limit on the number of rows and columns)
item Row Col Value
1230 0 abc
1230 1 abc
1230 2 bcd
1231 0 das
1231 1 das
1231 2 ada
123........
1235 0 xyz
1235 1 www
1235 2 xxx

I run a executeUpdate query to update this table with 6 rows and 3 columns.

Now if the user on the front end adds rows and columns to the array ie. the array size is now 10 X 12 (10 rows, 12 columns). My question is in this situation would it be a better design to -
1. delete the existing rows from the table and do inserts for all 10 X 12 values
2. do update for 6 X 3 grid and do inserts for the remaining 4 rows and remaining 8 columns.

Any ideas? Any help on this is highly appreciated.

Thanks,
Shweta

[Purushoth Thambu]

What database are you using? It's not good idea to delete the rows and insert again. The better approach will be to update existing rows and insert new rows as this will be performant when compared to delete+insert.

[Shweta Parekh]

Thanks for your response.
We are using Oracle DB.

-Shweta

[Virag Saksena]

And before updating, check if the rows have actually changed, rather than doing a needless update.