0

I just got stuck in a problem, where there are two ways of solving this.

Let me first explain the case,

I have a DB table consisting of some columns say id, name, address, priority. Here name and address is not unique but name + address + priority is unique.

Input provided to me is name and list of addresses. Now, what I have to do is to arrange name and address in the same order as given in input in my DB table.

There are two ways of solving:

  1. selecting on the basis of name and address and make update queries for those data which are changed and execute them.
  2. delete the data corresponding to name and address from table and insert the data with new priority.

I know that one update is faster than delete + insert but here in this case there is one select query too.

My intuition is that 1st method will be more fast but I don't have any technical details about it.

Am I missing something?

pozs
  • 34,608
  • 5
  • 57
  • 63
Geek_To_Learn
  • 1,816
  • 5
  • 28
  • 48
  • Are you using MySQL or Postgresql? Don't expect the same answer for different products... – jarlh Jan 08 '16 at 10:26
  • 2
    Note that these two mehods are NOT functionally identical! 1. one will trigger update triggers, whereas the other will trigger delete+insert triggers, 2. delete+insert will have different key IDs and will remove any FK relationships. – Piskvor left the building Jan 08 '16 at 10:29
  • @jarlh: I am using Postgresql – Geek_To_Learn Jan 08 '16 at 10:31
  • So please remove the MySQL tag. – jarlh Jan 08 '16 at 10:31
  • 1
    There is a 3rd option. Load the new data into a staging table, do what you need to do there, and write to your real table from the staging table. Don't sacrifice accuracy for speed. – Dan Bracuk Jan 08 '16 at 10:35
  • What you want to do is some bluk [`UPSERT`](http://stackoverflow.com/questions/17267417/how-do-i-do-an-upsert-merge-insert-on-duplicate-update-in-postgresql) – pozs Jan 08 '16 at 11:12
  • 1
    What exactly is "*arrange name and address in the same order as given in input*" supposed to mean? Rows in a table don't have any order, so what exactly do you mean with "*arrange in same order*"? Can you show us some sample data and the expected result after you run your statements? If your table is referenced by foreign keys, you might not be able to do the delete/insert approach in the first place. I also don't see why an update requires a select before? –  Jan 08 '16 at 11:18
  • `... arrange name and address in the same order as given in input in my DB table.` Wrong. There is no order in a DB table. Besides: your input doesn't contain the `priority` field, which is needed to make it unique, or to address unique rows in your DB table. – joop Jan 08 '16 at 11:19

0 Answers0