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
– Tim Biegeleisen, Commented Feb 27, 2017 at 9:31
But as a side note, 27 columns sounds a bit on the large side. You might want to restructure your database. — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 27, 2017 at 9:33
I don't think this is possible in a single query. You need to specify column names in UPDATE — Krishnakumar
– Krishnakumar, Commented Feb 27, 2017 at 9:34
Possible duplicate of How to UPDATE all columns of a record without having to list every column — Sebastian Brosch
– Sebastian Brosch, Commented Feb 27, 2017 at 9:34

Community · Accepted Answer · 2017-05-23 12:25:07Z

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, 2017 at 12:25

CommunityBot

11 silver badge

answered Feb 27, 2017 at 9:35

Arockia Nirmal

7871 gold badge7 silver badges20 bronze badges

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

Comments

Whencesoever · Accepted Answer · 2017-02-27 09:31:54Z

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, 2017 at 9:31

Whencesoever

2,30617 silver badges26 bronze badges

Collectives™ on Stack Overflow

insert single value across multiple columns in sql

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related