SQL update query - Can you not specify the column names?

Question

Hi

Is it possible to write a update statement and not give the columns names. For example

UPDATE tbl VALUES('1','2','3','4') WHERE id = 1;

The number of values will always match the column count.

Thanks in advance.

EDIT

I don't know the column names only the number of columns.

I know i could delete the row and then do an insert be then the id(which is A_I) wont be the same.

Please help.

Answer 1

...know i could delete the row and then do an insert be then the id(which is A_I) wont be the same...

Assuming that your first column is an AUTO_INCREMENT NOT NULL.

INSERT into tbl VALUES(NULL, val1, val2);

Answer 2

Even if it is possible, why would you want to do that? If you want to use the same query form for updates and inserts you can use "replace into tbl_name (column_names...) values (values...)"

Answer 3

Don't ever try to do that.

That will make your code hard to read and impossible to maintain.
If you later change the columns of your table all your code will fall apart and since you don't have the column names in the code you will not be able to search for the changed columns and find where they are used.

Answer 4

No, it is not possible:

UPDATE Syntax

If you don't know the column names (which is rather strange), you could query the information schema:

INFORMATION_SCHEMA Tables

Answer 5

I'm not sure if it's possible in a simple SQL query. However, if you can create a stored procedure then you have options. I'm not going to research the options, but here's the example.

I know that Oracle has metadata that you can query, so you can get the column names of a table. (I think SQL server has something similar)

So, since you can query this metadata then you can loop through the column names, build a string with column names for your update statement and do an "execute" of that string

This has issues though: - security - SQL injection is possible since you are building a string to execute. Perhaps you could use bound variables or parameterize even the string you build and run - if you build a "hard-coded" UI, the moment the table changes and doesn't have default values for new columns or the columns are reordered, it will break. However, if you build the UI to read the metadata and then do the same thing to create the string to run then it should be fine.

Answer 6

You can retrieve the list of columns using:

SELECT  COLUMN_NAME 
FROM    INFORMATION_SCHEMA.COLUMNS C 
WHERE   TABLE_NAME = 'MyTable'

That allows you to build a new SQL query which checks for a value in each column. It's best to do this in a client, like a C#, Python or perl script.