UPDATE query without hardcoding column names

Question

Let's say mytable has 5 columns, id being the first one.

Is it possible to do an UPDATE without hardcoding the column names?

UPDATE mytable VALUES(4, "hello", 31.12, 4141.12, "gjhg") WHERE id = 4

I haven't found it in most tutorials.

Desired use case with sqlite3:

row = (4, "hello", 31.12, 4141.12, "gjhg")
c.execute('UPDATE mytable VALUES(?) WHERE id = ?', row, row[0])

No, looking at the [definition of the `UPDATE` statement](https://sqlite.org/lang_update.html), it appears the `SET column-name or column-name-list` bit is mandatory. — glibdud, Feb 28 '18 at 16:45
If you're working in Python, you can build the string dynamically in a variety of ways, or am I misunderstanding the question? — lurker, Feb 28 '18 at 16:46
@lurker: the fact is I don't want to hardcode the column names. I'm prototyping many solutions with my DB structure. As I know that `row` is built to always perfectly fit to the DB columns, I don't want to hardcode column names in `UPDATE mytable SET columnname1 = ..., columnname2 = ..., ...`. — Basj, Feb 28 '18 at 16:51
Right, that part I get. But at some point, SQL will need to know what column to use when you execute the UPDATE. When you say you don't want to hard code them, I assume they're still known at run time in some variable somewhere. So the UPDATE string can be generated. — lurker, Feb 28 '18 at 16:56
@lurker Let's say `row` comes from a `SELECT *` (thus this list contains the right columns in the right order!), is modified (columns for which I know the index), and then UPDATED back in the DB. — Basj, Feb 28 '18 at 16:57
So generating a string from `row` in that case that looks like the format of, `4, "hello", 31.12, 4141.12, "gjhg"` and pasting together a string that then looks like `UPDATE mytable VALUES(4, "hello", 31.12, 4141.12, "gjhg") WHERE id = 4` is not possible in Python? I know that's a dumb question, which probably means maybe I am still begin daft. — lurker, Feb 28 '18 at 17:05
@lurker: the problem is that `UPDATE ... VALUES(...)` syntax doesn't seem to work/exist. — Basj, Feb 28 '18 at 17:07
Right. You're trying to use the `INSERT` command syntax on `UPDATE`. Why not format the `UPDATE` statement [the way SQLite3 expects it](http://www.sqlitetutorial.net/sqlite-update/)? In the end, it's just a string that you can build with Python. — lurker, Feb 28 '18 at 17:09
@lurker because this requires to hardcode the columnnames list somewhere ;) — Basj, Feb 28 '18 at 17:10
I see. So then does this problem really boil down to how you obtain the column names of a table in SQLite3, in the order you want them, programmatically? — lurker, Feb 28 '18 at 17:12
@lurker The point is: we can `INSERT into mytable VALUES(1, 2, 3)` without specifyin the col names (it automatically fits the DB structure). I would like to do the same with UPDATE, that's just that. — Basj, Feb 28 '18 at 17:13
Yes I understand that. Unfortunately, there is no such `UPDATE` command that is the same format as the `INSERT`. So the next option is to know how to obtain the column names programmatically (through an SQLite3 statement) I would think... — lurker, Feb 28 '18 at 17:17
If you are wanting the columns in the order that you would get from a `select *`, then you could do `c.execute('pragma table_info(your_table_name)')` to get the column names. — lurker, Feb 28 '18 at 17:27

score 0 · Answer 1 · answered Feb 28 '18 at 17:01

As far as I know you cannot do that. According to https://sqlite.org/lang_update.html

However there are other ways of writing the query: https://stackoverflow.com/a/13482298/7791653

You could generate a query yourself.
First you select all the column names and do a for loop where you build a String like "column1, column1, column1" and add that to the appropriate place of the query. All you have to do then is something like

"UPDATE users
SET  (" + generatedQueryPart = ")
   = ('value1', 'value2', 'value3') 
WHERE some_condition ";

Hope this gave you some more information.

UPDATE query without hardcoding column names

1 Answers1