Oracle Update multiple columns with same value

Question

Lets suppose I created one table--

Create table t1 (aa varchar2(5),bb varchar2(5),cc varchar2(5));

Inserted values in it--

insert into T1 values ('a','b','c');
commit;

Now in one scenario, if i wanted to update all columns with same value then I am doing by this way--

UPDATE T1 SET AA='x',BB='x',CC='x';

Is there any another way by which this update task can be accomplished considering in real time there may be quite large number of columns and all has to be updated with same value in one go?

I am using Oracle 11.2.0.1.0 - 64bit Production

Note: Usually there are very less any scenarios where same values are being updated for all columns. But for example consider a school database and a good student scores 10/10 marks in all subjects. :-)

Thanks.

No, and why should there? You only build the query once.

juergen d
– juergen d

2015-09-08 10:07:57 +00:00
Commented Sep 8, 2015 at 10:07 — juergen d
– juergen d, Commented Sep 8, 2015 at 10:07

Lalit Kumar B · Accepted Answer · 2015-09-08 10:25:50Z

3

There is no way to do it in pure SQL. You must list down all the columns explicitly in the UPDATE statement.

And, believe me it is not a difficult task using a good text editor. Using the metadata you could get the list of column names in few seconds, all you need to do is prepare the SQL statement as per the syntax.

If you really want to do it dynamically, then you need to do it in PL/SQL and (ab)use EXECUTE IMMEDIATE. I would personally not suggest it unless you are just doing it for learning purpose.

answered Sep 8, 2015 at 10:25

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

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

Comments

Anh Pham · Accepted Answer · 2017-12-12 03:55:58Z

0

You could try this:

UPDATE T1 SET AA='x',BB=AA,CC=AA;

edited Dec 12, 2017 at 3:55

Anh Pham

2,1189 gold badges20 silver badges29 bronze badges

answered Dec 12, 2017 at 3:14

Maurice Zhang

1

1 Comment

Mogsdad Over a year ago

Welcome to Stack Overflow! Thank you for this code snippet, which might provide some limited, immediate help. A proper explanation would greatly improve its long-term value by showing why this is a good solution to the problem, and would make it more useful to future readers with other, similar questions. Please edit your answer to add some explanation, including the assumptions you've made.

Collectives™ on Stack Overflow

Oracle Update multiple columns with same value

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related