asked on

pl/sql column name is name, cannot use update statement

Is there a way to wrap the name of the column in pl/sql for Oracle 9i?
I have this table that has a column name NAME. The select statement is ok, but the update statement gives me invalid identifer error. If I put single quote around it, it doesn't compare the condition where name = 'abc' correctly. What can I do other than alter the name of the column and alter back? Thanks.

slightwv (䄆 Netminder)

I'm not sure I understand the problem here but you can, I don't recommend it, use double quotes. You need to capitilize the column name:

Select "NAME" from table.

ajexpert

I am not sure, but do you have table called NAME also you have column called NAME?

Can you check by running this query?

SELECT * FROM USER_OBJECTS
WHERE OBJECT_NAME = 'NAME'

jm76430

Try this:

create table temp (name varchar2(10));

insert into temp values ('test');

select * from temp;

update temp
set "NAME" = 'it works'
where "NAME" = 'test';

select * from temp;

ajexpert

Well, I do not see use of double quotes here (")

My try:

create table temp1 (name varchar2(10));

insert into temp1 values ('test');

select * from temp1;

update temp1
   set NAME = 'it works'
 where NAME = 'test';

Open in new window

slightwv (䄆 Netminder)

>>Well, I do not see use of double quotes here (")

I don't either but the asker says they want them for some reason.

That said: jm76430,

I believe double quotes has already been mentioned.

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anillucky31

You can try like this

update yourtable

set yourtable.Name = 'somevalue'
where yourtable.Name = 'something'

cmleung2

ASKER

No I wasn't trying to do the dynamic sql, but I guess I have to now, and it works. thanks.