SQL & PL/SQL :: Update Name With Single Space Between Words
Mar 26, 2010
In the table the names are having diffrent spaces for one name only one space between the words,for another name two spaces between words,for other names three spaces between the words.
I want to update with single space how can we that.
I am giving the data as follows.
CREATE TABLE student(sname VARCHAR2(30));
INSERT INTO student VALUES('PRAKASH BABU');
INSERT INTO student VALUES('RAJESH KUMAR');
INSERT INTO student VALUES('POORNA CHANDAR');
INSERT INTO student VALUES('ANIL RAJ');
INSERT INTO student VALUES('ABHI RAM KONA');
INSERT INTO student VALUES('SRI TEJA MEDI');
Like this the table contains millions of rows.
I want to update the names like this.
PRAKASH BABU
RAJESH KUMAR
POORNA CHANDAR
ANIL RAJ
ABHI RAM KONA
SRI TEJA MEDI
I have the following table and data , So i want to update this table(word column) with words that has same soundex with one word of them, the word must be that has the long length if the two words has same length So, update by any of them.
drop table t1; create table t1 (id number(10) ,word varchar2(100));
insert into t1 values(1,'Londn'); insert into t1 values(2,'Egypt'); insert into t1 values(3,'London'); insert into t1 values(4,'Gorgey'); insert into t1 values(5,'Michal'); insert into t1 values(6,'Michel'); insert into t1 values(7,'London'); insert into t1 values(8,'Egpt'); insert into t1 values(9,'Londan'); insert into t1 values(10,'Gorgy'); insert into t1 values(11,'Gorge');the results must be as following id word 1 London 2 Egypt 3 London 4 Gorey 5 Michal 6 Michal 7 London 8 Egypt 9 London 10 Gorey 11 GoreyNote: this is the results of soundex
i am reading the columns value from different table but i want to update it with single update statement. such as how to update multiple columns (50 columns) of table with single update statement .. is there any sql statement available i know it how to do with pl/sql.
I have a column "empno" in EMP table and "deptno" in DEPT table . I want to update both the columns with single UPDATE statement. With out a creation of stored procedure or view(updating it through view).
I'm getting back more than I want. I need to get the latest row in the PS_ACAD_STDNG_ACTN that has the academic standing code for students.
I thought if I max the effdt, strm, and effseq I would get back only one row. Especially effdt since a academic status is hardly ever updated on the same date.
PS_NTSR_GF_STUFILE will have multiple emplid's for students taking classes. PS_ACAD_STDNG_ACTN should have the last standing status for each student..(PRO = Probation, DIS = Dismissed).
UPDATE PS_NTSR_GF_STUFILE a SET a.NTSR_GF_ENRL_STAT = nvl(( SELECT b.GBSA_SUB1 FROM PS_GBSA_DTL b, PS_ACAD_STDNG_ACTN c WHERE c.ACAD_STNDNG_STAT = b.GBSA_VALUE
i have to update a single column(x.c) in x table.here the condition is x.a is not null and x.b is not null x.d is null then update x.c=x.b for each row.
Can I achieve the merge (update-only) and delete in a single SQL statement?
merge into table_4 using (select table_3.col1 col1,table_3.col2 col2, from table_1, table_2, table_3 where [code].....
delete from table_3 where <records identified in the 'using' clause of above merge>;
i.e.
delete from table_3 where (table_3.col1,table_3.col2) in ( select table_3.col1,table_3.col2, from table_1, table_2, table_3 where join conditions..... other conditions .... group by table_3.col1, col1,table_3.col2 having count(*) >1 );
What I want is roughly as following :
WITH mywith as ( select table_3.col1,table_3.col2, from table_1, table_2, table_3 where join conditions..... other conditions .... group by table_3.col1, col1,table_3.col2 having count(*) >1 ) update table_4 set col3='N' where table_4.col1=mywith.col1 and table_4.col2=mywith.col2 delete from table_3 where table_3.col1=mywith.col1 and table_3.col2=mywith.col2;
I would like to insert a value if that value is not existing in the table (example for a column which contains date only new dates should be inserted and if the date already exists in the column then it needs to get updated )
example of scenario...
date s1 s2 s3
in the above if the date is new..it should get inserted with the appropriate slot no.(s1,s2,s3) if the date already exists it needs to update the no.in slot no.
We have to update a single column data in about 10 tables which has child/parent table relations, pk/fk constraints.. The column that we are updating is a part of primary key in half of the tables and part of foreign key in the other half tables.. I'm thinking of disabling all the foreign key constraints in the tables then update the column data then enable the foreign key constraints in these tables.
I have two update queries in the same Proc. One Seems to run just fine, the other I am getting this error:
ORA-01427: single-row subquery returns more than one row
The working Updates' structure is the same as the erroneous one. This works:
UPDATE P0525_STOREROOM_HOLDER H SET H.STRATIFICATION_ID = (SELECT X.STRATIFICATION_ID FROM EMISTRATIFICATION_XS X WHERE H.TOA = X.TOA AND H.STOREROOM = X.STOREROOM AND H.NSN = X.NSN AND X.ASSEMBLY = 'NO REQUIREMENT' );
This one gives me a single-row error:
UPDATE P0525_STOREROOM_HOLDER H SET H.STRATIFICATION_ID = (SELECT X.STRATIFICATION_ID FROM EMISTRATIFICATION_XS X WHERE H.TOA = X.TOA AND H.STOREROOM = X.STOREROOM AND H.NSN = X.NSN AND X.ASSEMBLY = 'ABOVE ALLOWANCE' AND H.NSN_QUANTITY > 0);
I have run a check on the data and there doesn't appear to be any duplicate values in the second update... Both Updates are supposed to be updating record sets not a single row (i.e. the stratification_id where the criteria matches...
Updating multiple ROWS with different values using single statement. Requirement is to update one column in a table with the values in the other table.
Say we have 3 tables, CORPORATION,CORPORATE PROFILE and MEMBER.
Each MEMBER has CORPORATE PROFILE which in turn is associated with CORPORATION. Now I need to update MEMBER table with CORPORATION identifier for members who belong to corporations with identifiers say 'ABC' and 'DEF'.
MEMBER table contains column 'CORPIDENTIFIER '. CORPORATEPROFILE table contains MEMBERID and CORPORATIONID,this will associate a member with the corporation. CORPORATION table contains ID and CORPIDENTIFIER.
Using the below query I am getting error,ORA-01427:single-row subquery returns more than one row
UPDATE MEMBER M SET M.CORPIDENTIFIER= (SELECT A.IDENTIFIER FROM CORPORATION A,CORPORATEPROFILE B WHERE B.CORPORATIONID=A.ID AND B.MEMBERID=M.ID AND (A.IDENTIFIER LIKE 'ABC' OR A.IDENTIFIER LIKE 'DEF'))
Sub query in the above query returns multiple rows and hence it is throwing the error.More than one members are associated with Corporations ABC and DEF. Is there any way possible to update all the rows in single query with out iterating the result set of sub query.
I have two table master and detail i want to update both tables with update command in master i want to update voc_date and in detail i want to update item_code in one command but i am not understand how to control this query for example when i use this command
1). update master set voc_date=sysdate it works very well 2). update detail set item_code='12345' it also work very well but i want to update master,detail table in one query pls guide me with some query example
Sysaux Tablespace is running low. WE SET AWR RETENTION TIME=60 DAYS. WE ARE NOT INTEREST TO EXTEND SYSAUX TABLESPACE SIZE. Usually we take AWR weekly once. Some times we did ADDM report and ASH.
CODEsql>select TABLESPACE_NAME, FILE_NAME, BYTES/(1024*1024), AUTOEXTENSIBLE, MAXBYTES/(1024*1024) from dba_data_files where tablespace_name = 'SYSAUX';
1. What's the best SOLUTION ? 2. Can i shrink sysaux tablespace ? 3. I think , The size for all occupants in sysaux tablespace is less than 200 MB => how to find actual content of sysaux tablespace ? 4. What could be the reason for growth? Is there any way to free the space from sysaux table space?
I tried to convert numbers to words, it shows the below error.
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 6 11:00:29 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT TO_CHAR (TO_DATE (2447834, 'j'), 'jsp') FROM DUAL;
TO_CHAR(TO_DATE(2447834,'J'),'JSP') --------------------------------------------------------------------------- two million four hundred forty-seven thousand eight hundred thirty-four
SQL> SELECT TO_CHAR (TO_DATE (244783400, 'j'), 'jsp') FROM DUAL; SELECT TO_CHAR (TO_DATE (244783400, 'j'), 'jsp') FROM DUAL * ERROR at line 1: ORA-01830: date format picture ends before converting entire input string
I found a message which explain how to spell out numbers to words. I'm french and the purpose of my question is how to convert numbers to word (to print cheque) but in french the function found in the newsgroup was :
I have a table with the following column and data is like this.
SQL>CREATE TABLE test ( column1 varchar2(50)); SQL>INSERT INTO test VALUES('ABC XYZ'); SQL>INSERT INTO test VALUES('MNO PQR'); SQL>INSERT INTO test VALUES('ABCD ABC'); SQL>INSERT INTO test VALUES('PQR MNOP');
When integrating your product with third-party APIs I've repeatedly come into conflict with PLSQL and Oracle's reserved words list. For instance while integrating the clickatell SMS Message Transmission notification API there are two variables they send.
[URL]......
How do you propose PLSQL handles these requests without using Apache to rewrite the variable names, or using the three and four variable pslql variable collection methods.
