insert single value across multiple columns in sql

Question

I have a table with almost 27 columns(all of integer datatype) with first column being ID(PK). I wish to update all the values to some value say 500. is there any query to update without typing all the 27 column names like this.

UPDATE tbl_name SET a=500,b=500,c=500.....z=500 where ID = 1

Or else can i make any PL/SQL function which will fetch next next column by itself without providing the column name and set the value ?

AFAIK this is the only option, unless you want to use dynamic SQL. If so, then you might be able to loop over all columns and assign `500` to each one. — Tim Biegeleisen, Feb 27 '17 at 09:31
But as a side note, 27 columns sounds a bit on the large side. You might want to restructure your database. — Tim Biegeleisen, Feb 27 '17 at 09:33
I don't think this is possible in a single query. You need to specify column names in UPDATE — Krishnakumar, Feb 27 '17 at 09:34
Possible duplicate of [How to UPDATE all columns of a record without having to list every column](http://stackoverflow.com/questions/29926765/how-to-update-all-columns-of-a-record-without-having-to-list-every-column) — Sebastian Brosch, Feb 27 '17 at 09:34
Refer below link : http://stackoverflow.com/questions/9079617/update-multiple-columns-in-sql — Mansoor, Feb 27 '17 at 09:34
PL/SQL is Oracle only (not mysql as tagged) and no that's not what is meant by dynamic sql. — P.Salmon, Feb 27 '17 at 09:53

score 2 · Accepted Answer · edited May 23 '17 at 12:25

2

It is not possible in any db's. You need to mention the column names. Check the update specifications for most common db's mentioned in the below thread

How to UPDATE all columns of a record without having to list every column

edited May 23 '17 at 12:25

Community

1
1

answered Feb 27 '17 at 09:35

Arockia Nirmal

737
1
6
20

score 1 · Answer 2 · answered Feb 27 '17 at 09:31

1

You cannot do this. You always have to specify the column names while updating. If it is a proccess You will be reapeating You can write stored procedure doing it.

answered Feb 27 '17 at 09:31

Whencesoever

2,218
15
26

insert single value across multiple columns in sql

2 Answers2