Change only single column values when we use * in sql

Question

Is there a way to change only single column values as updated in selection of oracle sql.

SELECT  ORDD.id||'sdaf' as id,ORDD.* 
FROM RDT_ORDER ORDM,RDT_ORDERDETAIL ORDD;

Here in RDT_ORDERDETAIL am having around Hundered columns but only single coulmn I need as updated.

I don't want to select all Hundred columns by writing all columns in select query as below

SELECT  ORDD.id||'sdaf' as id,ORDD.col1,ORDD.col2,ORDD.col3,ORDD.col4 
FROM RDT_ORDER ORDM,RDT_ORDERDETAIL ORDD;

Please note I have removed where clause,I need to get single updated column values other columns as it is.But I shouldn't write all coulmns in Select.

Existing DB Records :

ID NAME COL3 COL4
1  name1 ..   ..
2  name2 ..   ..
3  name3 ..   ..

Now Result :

ID       ID NAME COL3 COL4
1_'sdaf' 1  name1 ..   ..
2_'sdaf' 2  name2 ..   ..
3_'sdaf' 3  name3 ..   ..

Expected Result :

ID        NAME COL3 COL4
1_'sdaf'  name1 ..   ..
2_'sdaf'  name2 ..   ..
3_'sdaf'  name3 ..   ..

`single column values as updated in selection of oracle` by this you mean, you want to select only the columns that you updated in your UPDATE query previously? — Maheswaran Ravisankar, Feb 27 '14 at 09:11
What exactly you need to find? What is the point in your join (cartesian!!)? Why dont you have ORDD.COLNAME? Provide some examples and explain clearly — Srini V, Feb 27 '14 at 09:15
Please check the udpated Question.Am not updating anything.this is not related to updation. — sunleo, Feb 27 '14 at 09:24
Why do want to view them this way? Why not update the table in the DB? Generally speaking, using `SELECT *` is frowned upon anyways. I'm also a bit concerned over the fact that your table has 100 (or more) columns - most dbs don't need anywhere near that many in a single table (especially for something that appears to be a transactional database). — Clockwork-Muse, Feb 27 '14 at 10:37

score 2 · Answer 1 · answered Feb 27 '14 at 09:41

2

I don't think you can exclude a single column by quering with select * from.... May be a VIEW can help you on this.

CREATE OR REPLACE VIEW <view_name> AS
     SELECT  ordd.id||'sdaf' AS id, ordd.col1, ordd.col2, ordd.col3, ordd.col4 
     FROM    rdt_order ordm,
             rdt_orderdetail ordd
     WHERE   .....;

answered Feb 27 '14 at 09:41

Dba

6,511
1
24
33

I would give a +1 for suggesting another alternative. Since you have posted a minute before mine, I removed this content. I do not suggest views unless it is not used frequently. Unfortunately reached the limit for voting today :( – Srini V Feb 27 '14 at 09:51

score 2 · Accepted Answer · edited May 23 '17 at 11:57

If you want to select all but one rows then the answer is No - you either get all fields (*) OR specify the fields you want. This is how Oracle works.

If you still persists, then there might be some work around like Dynamic SQL using query generator or data cartridge

You can refer here for similar question.

If you want to have a string manipulation and all columns you can do using alias.

SELECT COL1||'_asd_', T.*
FROM MYTABLE T;

Your idea of getting all columns from a table with a manipulation on one or more columns without writers cramp is not possible directly. You may try dynamic SQL but I will not suggest that for anything because of low maintainability and its a head ache on a long run.

Change only single column values when we use * in sql

2 Answers2