asked on SQL code to remove content from a field in DB needed
Hi,
Can someone provide me with a SQL statement to remove content from 1 field in my DB.
I have a table called SHOP and withing that table I have Project field.
So this "Project" field should have no data at all, but some of our emp. but the letter "H" in it and how I cannot delete it.
So I want field "Project" to be " " instead of having "H" in it.
Thanks
Can someone provide me with a SQL statement to remove content from 1 field in my DB.
I have a table called SHOP and withing that table I have Project field.
So this "Project" field should have no data at all, but some of our emp. but the letter "H" in it and how I cannot delete it.
So I want field "Project" to be " " instead of having "H" in it.
Thanks
MySQL Server
Last Comment
arnold
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
hi,
can I write something like this:
update shop
set project = ' ' ,
/
commit
/
can I write something like this:
update shop
set project = ' ' ,
/
commit
/
yah you can... but then project field wont be called empty.. coz it has a value ' ' (space even)...
ASKER
how about "insert" command do I need to you it?
no... you do not want to insert any new row....just update the existing ones
ASKER
here is the whole script:
insert into shop
select * from shop
where line2 = '31944'
/
commit
/
update shop
set project = ' ',
where line2 = '31944'
/
commit
/
is this the correct format?
insert into shop
select * from shop
where line2 = '31944'
/
commit
/
update shop
set project = ' ',
where line2 = '31944'
/
commit
/
is this the correct format?
ASKER
ok,
So I typed
update shop
set project = ' ',
where line2 = '31944'
/
and I got error on line 3 "Invalid user.table.column"
So I typed
update shop
set project = ' ',
where line2 = '31944'
/
and I got error on line 3 "Invalid user.table.column"
remove the comma at the end of 3rd line i.e. Set project=''
ASKER
i got the error saying
virtual column not allowed here
update shop
set project = ' '
where line2 = '31944'
/
virtual column not allowed here
update shop
set project = ' '
where line2 = '31944'
/
is SHOP a view?
ASKER
I don't quiet understand you on this.
SHOP is a table name.
SHOP is a table name.
which database are you working with? MYSQL? MS SQL Server or oracle?
>>virtual column not allowed here
this error i saw in oracle.. .when we try to update a computed column of a view.. which is offcourse not updateable...
>>virtual column not allowed here
this error i saw in oracle.. .when we try to update a computed column of a view.. which is offcourse not updateable...
ASKER
it is oracle db,
you you think the parent table is on SHOP ?
you you think the parent table is on SHOP ?
that means SHOP is not a table... it is a view.. can you do desc SHOP in sql plus to see ... it should give you the query behind this table... from there you can see the actual table which contains project column.. .and then use the above statement on that table.
Do you have another table that has a project field? IS there a row that has an H in it? Update that table and see whether the changes are now reflected in the SHOP view.
ASKER
OK,
so I did DESC SHOP and it gave me bunch of fields. One of them is Project and the other one is LINE2
Project NULL ? (is blank) and Type (VARCHAR2(10))
LINE2 NULL ? (NOT NULL) and Type (VARCHAR2(12))
So looks like SHOP is a table
so I did DESC SHOP and it gave me bunch of fields. One of them is Project and the other one is LINE2
Project NULL ? (is blank) and Type (VARCHAR2(10))
LINE2 NULL ? (NOT NULL) and Type (VARCHAR2(12))
So looks like SHOP is a table
ASKER
Arnold, I cannot change the value "H" from front-end. Trying to do it through sql statement......
I have a lot of tables, so I don't know if I have Project in differernt table.
I am pretty sure that it should be replaced in SHOP table......
thanks
I have a lot of tables, so I don't know if I have Project in differernt table.
I am pretty sure that it should be replaced in SHOP table......
thanks
select text from all_views where view_name='SHOP';
write this.. i forgot that desc command will only show the column even for views... i dont work with sqlplus...sorry for that.. above query should return you the sql which is behind SHOP view....
i am positive that SHOP is a view
write this.. i forgot that desc command will only show the column even for views... i dont work with sqlplus...sorry for that.. above query should return you the sql which is behind SHOP view....
i am positive that SHOP is a view
ASKER
here is the answer, but I don't know what that means:
TEXT
--------------------------
select LINE2 LINE2,
RELEASE_NO
it gave my my LINE2 field.....
TEXT
--------------------------
select LINE2 LINE2,
RELEASE_NO
it gave my my LINE2 field.....
so its a view...
okay now... we are close... but sqlplus is not showing u all the data... run following to get the output in a file and attach the file here
spool c:\view_details.txt
select text from all_views where view_name='SHOP';
spool off;
okay now... we are close... but sqlplus is not showing u all the data... run following to get the output in a file and attach the file here
spool c:\view_details.txt
select text from all_views where view_name='SHOP';
spool off;
ASKER
here it is....
VIEW-DETAILS.TXT
VIEW-DETAILS.TXT
ASKER
my line2 is order_no
sorry for confusion
sorry for confusion
now we are having trouble with sqlplus display settings... i thought it would give full output in the file.. but it didn't.... try this
set wrap on
spool c:\view_details.txt
select text from all_views where view_name='SHOP';
spool off;
try again now...i am at home and do not have access of sqlplus... let see if this works..
set wrap on
spool c:\view_details.txt
select text from all_views where view_name='SHOP';
spool off;
try again now...i am at home and do not have access of sqlplus... let see if this works..
ASKER
same result
IMHO, it is better to have an empty field: "" rather than null.
The above checks if project has anything it will be changed.
You can run the update query without a where clause and it will update every project column in every row.
Note " " is not nothing it is a space. Nothing is "" i.e. empty set.