We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

cmleung2
cmleung2 asked
on
Medium Priority
564 Views
Last Modified: 2013-12-07
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.
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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.

Commented:
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'

Commented:
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;

Commented:
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

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>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.
Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
You can try like this

update yourtable

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

Author

Commented:
No I wasn't trying to do the dynamic sql, but I guess I have to now, and it works.  thanks.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.