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, it appears the SET column-name or column-name-list bit is mandatory. — glibdud
– glibdud, Commented Feb 28, 2018 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
– lurker, Commented Feb 28, 2018 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
– Basj, Commented Feb 28, 2018 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
– lurker, Commented Feb 28, 2018 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
– Basj, Commented Feb 28, 2018 at 16:57

Oguzcan · Accepted Answer · 2018-02-28 17:01:31Z

0

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.

answered Feb 28, 2018 at 17:01

Oguzcan

4306 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

UPDATE query without hardcoding column names

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related