3

I have three tables A, B and C.

A:
----------------
index_a | value
----------------

B:
----------------
index_b | value
----------------

C:
----------------------------
index_c | table_name | value_c
----------------------------

Now user gives me value ,Table name and new_value_c. Table name can be A or B and value is respective value of table A or B. Now if there exist a record matching given value in given table we will get index of that table (index_a or index_b) and try to insert that in table C if there is no record with that index and table name otherwise we will update new_value_c. If there is no record matching value and table name then we will not do nothing.

I know we can use one SELECT to get index of table A or B and then INSERT OR REPLACE INTO table C. I am wondering if it can be achieved in single sql command.

Nishant Kumar
  • 2,199
  • 2
  • 22
  • 43
  • 2
    possible duplicate of [SQLite - UPSERT \*not\* INSERT or REPLACE](http://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace) – aleroot Sep 15 '15 at 07:14
  • In passing, and without knowing where your table structure came from nor why, my first thought is that it may be better with one "AB" table with columns `index`, `type` and `value`. This would almost certainly simplify your SQL, particularly if there were ever more than just "A" and "B" tables in the future. – TripeHound Sep 15 '15 at 08:21
  • @aleroot i think my question is different from that link although it uses that but it need more then that. – Nishant Kumar Sep 15 '15 at 08:57

1 Answers1

2

You can use INSERT OR REPLACE and SELECT in one statement. Try this:

INSERT OR REPLACE INTO C (index_c, table_name, value_c)
SELECT index_a, 'A', ?3 FROM A WHERE value = ?1 AND 'A' = ?2
 UNION
SELECT index_b, 'B', ?3 FROM B WHERE value = ?1 AND 'B' = ?2

There: ?1 - placeholder for value, ?2 - table name and ?3 - new_value_c

Rimas
  • 5,904
  • 2
  • 26
  • 38