how to over come this error, because i need to update only 3 columns in the table and iam getting error when iam updating like this
update country1 set cname='japan','usa'
where cid=100,101
ERROR:ORA-01747,INVALID USER.TABLE.COLUMN,TABLE.COLUMN,OR COLUMN
SPECIFICATION
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.
View 5 Replies View RelatedI need to write a script which copies 4 col data from one table to another table. there are three tables
cwat_curr_mst and cwat_assigned_customer and cwat_assignment_mst.
Cwat curr mst has PK curr_id and cwat_assigned_customer has PK assignment_id.
Also cwat_assigned_customer has customer_id.
In cwat_assignment_mst has Curr_id and Assignment_ID.
cwat_curr_mst and cwat_assigned_customer tables has 4 cols in common
they are
ASRT_SNM_NO, SNM_NO, FLORIDA_NO, CBRN_NO.
So from curr_mst all these 4 cols data needs to come/copy into cwat_assigned_customer.
I am trying to update multiple columns from one table based on the results of another table So I have 3 tables as follows
HISTORYSUMM_SNAPADM_CHOICE
My SQL code is loosely SELECT SUM(H.HIS1),
SS.SNAP1,
AC.ADM1FROMHISTORY H,
SUMM_SNAP SS,ADM_CHOICE ACWHERE H.HIS2=SS.SNAP2AND SS.SNAP3=AC.ADM2GROUP BY SS.SNAP1,
AC.ADM1
This works, and I am able to SUM the column as I need with the right numbers. I altered the SUMM_SNAP table and now I want this summarized column to be in the table I tried using UPDATE, but there is no FROM clause to let me do the table join/group by
UPDATE SUMM_SNAPSET SUMM_SNAP.SNAP3=SUM(H.HIS1)FROMHISTORY H,
SUMM_SNAP SS,ADM_CHOICE AC
WHERE H.HIS2=SS.SNAP2AND SS.SNAP3=AC.ADM2
GROUP BY SS.SNAP1, AC.ADM1
The above is obviously wrong - but just trying to show whatI was thinking What would be the best method to get the numbers from the SUM into a table?
i have two tables test1 and test2. i want to update the column(DEPT_DSCR) of both the tables TEST1 and TEST2 using select for update and current of...using cursor.
I have a code written as follows :
DECLARE
v_mydept1 TEST1.DEPT_CD%TYPE;
v_mydept2 TEST2.DEPT_CD%TYPE;
CURSOR C1 IS SELECT TEST1.DEPT_CD,TEST2.DEPT_CD FROM TEST1,TEST2 WHERE TEST1.DEPT_CD = TEST2.DEPT_CD AND TEST1.DEPT_CD = 'AA' FOR UPDATE OF TEST1.DEPT_DSCR,TEST2.DEPT_DSCR;
[code].......
The above code when run says that it runs successfully. But it does not updates the desired columns[DEPT_DSCR].
It only works when we want to update single or multiple columns of same table...i.e. by providing these columns after "FOR UPDATE OF"
I am not sure what is the exact problem when we want to update multiple columns of different tables.
I am trying to update a table column values if any change occurs using bulk collect and for all update not able to get idea. below is the proc working out.it is for insert and update using the cursors.
CREATE OR REPLACE PROCEDURE PRC_INS(P_ID IN NUMBER,P_STAT OUT NUMBER) IS
TYPE T_TEST_TAB IS TABLE OF T_DTLS%ROWTYPE;
V_PARAM T_TEST_TAB;
V_STATUS NUMBER;
V_BUS VARCHAR2(20);
V_UP VARCHAR2(1);
V_Q VARCHAR2(50);
[Code]....
How to merge multiple rows into single row (but multiple columns) efficiently.
For example
IDVal IDDesc IdNum Id_Information_Type Attribute_1 Attribute_2 Attribute_3 Attribute_4 Attribute_5
23 asdc 1 Location USA NM ABQ Four Seasons 87106
23 asdc 1 Stats 2300 91.7 8.2 85432
23 asdc 1 Audit 1996 June 17 1200
65 affc 2 Location USA TX AUS Hilton 92305
65 affc 2 Stats 5510 42.7 46 9999
65 affc 2 Audit 1996 July 172 1100
where different attributes mean different thing for each Information_type. For example for Information_Type=Location
Attribute_1 means Country
Attribute_2 means State and so on.
For example for Information_Type=Stats
Attribute_1 means Population
Attribute_2 means American Ethnicity percentage and so on.
I want to create a view that shows like below:
IDVal IDDesc IDNum Country State City Hotel ZipCode Population American% Other% Area Audit Year AuditMonth Audit Type AuditTime
23 asdc 1 USA NM ABQ FourSeasons 87106 2300 91.7 46 85432 1996 June 17 1200
65 affc 2 USA TX AUS Hilton 92305 5510 42.7 46 9999 1996 July 172 1100
I am attempting to select back multiple values for a specific key on one row. See the example below. I have been able to use the sys_connect_by_path to combine the fields into one field but I am unable to assign them to fields of their own. See the example below
TABLE DETAILS:
Policy id plan name
111 A Plan
111 B Plan
111 Z Plan
112 A Plan
112 Z Plan
My desired result is to be able to show the output as follows
Policy ID Plan_1 Plan_2 Plan_3
111 A Plan B Plan Z PLan
112 A Plan Z PLan
I have one doubt about update command in sql. How to update the multiple rows with different values using update statment.
Eg:-
SQL> set linesize 500;
SQL> set pagesize 500;
SQL> select * from emp;
SQL> select empno,ename,sal from emp;
SQL> select empno,ename,sal from emp;
EMPNO ENAME SAL
---------- ---------- ----------
7839 KING 5000
7698 BLAKE 2850
7782 CLARK 2450
7566 JONES 2975
7654 MARTIN 1250
[Code]....
The above table contains 14 records. Now i would like to update the salary column with different values like
EMPNO SAL
===========
7839 18000
7698 20000
7782 5000
...
...
...
7934 25000
How to update above values with single update query.
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).
View 4 Replies View Relatedhow does sorting on multiple columns work
suppose my query is
select * from person order by first_name desc
and sys_person_id asc
this query works , but is this write way to sort on multiple column ?
I have a query to pull the first contact of students.
The table has all contacts like parent/guardian, friends family, emergency contact etc.
I would like to the first primary contact in this order,
1. initial contact, 2. same as student address and also have to be parents,
3. live with and also a parent, 4. parents 5. friends.
I don't know how to pull 2 and 3 . because it looks like it needs to concatenate the columns.
here is my initial query
Select Min(U2.Id) Keep (Dense_Rank First Order By U2.Initial_Contact Desc, U2.Same_As_Students_Address Desc,u2.lives_with DESC,U2.Guardian Desc)
From Contacts
how to achieve 2 and 3?
The table script is attached. All the above columns are 1 or 0.
I am trying to validate a monthly report so was trying to write queries to get different criteria into one table. So my first query returns all the product,second query returns all the enrolled customers, 3rd query returns all the cancelled customers and 4th query returns all the newly enrolled for a month. Is there a way I can pass the first query results into 1st column, 2 query results into 2nd column, 3 query results into 3rd column and so on.
I tired writing the SQL several different ways and have spent a day on it and still cannot figure it out. I am using SQL Developer.
I have a table with Column A, B, C. I want to write a query to retrieve the top row of A, B combination. i.e, for every unique value of A,B combination I want the row having highest value for C. I tried using rank() function but am not able to get the top row with combination of A,B.
View 8 Replies View Relatedselect * from nrc_trans_descr where type_id_nrc=60013 -- it has 18 columns and i have hard coded 60013 for simplification here.60013 is derived from 3 other table Output is ( it can have many rows too.typically for each type_id_nrc there is one row ).
TYPE_ID_NRC TRIGGER_STATUS INSTALLMENT_TYPE_ID_NRC
---------------------------------------------------------------------
60013 0 61013
i have to pass TYPE_ID_NRC and INSTALLMENT_TYPE_ID_NRC to restriction_id column in a different table. currently i am doing like this
select * FROM DISCOUNT_RESTRICTIONS WHERE discount_id in (12085,12086)
and (restricted_id in ( select type_id_nrc from nrc_trans_descr where type_id_nrc=60013)
or restricted_id in ( select installment_type_id_nrc from nrc_trans_descr where type_id_nrc=60013));
am using ORACLE 10GR2(solution for 11gr2 is welcome too)
Table-Name
ID Status description Tracking ID
1 Strat Frog 1
2 Start Dog 2
3 Process Frog 1
4 Completed Dog 2
5 Start Rabbit 3
6 Error Frog 1
7 Stop Rabbit 3
8 Start Elephant 4
9 process Elephant 4
10 Start Human 5
11 Stop Human 5
12 Start Butterfly 6
13 completed Butterfly 6
14 start lion 7
15 error lion 8
16 complted lion 8
17 start tiger 9
18 error tiger 9
select * from Table-Name where datetime < to_date('2012/12/06:06:00:00', 'yyyy/mm/dd:hh24:mi:ss')
And datetime > to_date('2012/12/04:22:00:00', 'yyyy/mm/dd:hh24:mi:ss')And not description in (Select * from Table-Name where Status like ('%Complete%' or Status like '%stop%') and description in (Select description from Table-Name where Status Like '%start%'));
Result should be " Frog and Elephant and tiger"
Start of every record(descrpition --status is Start)
End of every record ( status is stop or done or completed)
status process is in btwn (their will be mulitple records with name s//y to process...ie. process 1 ...process 2...process 3 )
Note:
tracking IDs may change up on error
Can i select multiple columns while using group by single column?
View 5 Replies View RelatedI've read so many different pages on this topic but I can't seem to get my query the way it needs to be. Here's the query:
select admitnbr, lastname||', '||firstname||' '||finitial, hphone, mobile, wphone, med_rec, dob
from patients join schedule using (key_patien)
join adtmirro using (key_patien)
where appt_state = 'ON HOLD'
Because patients in my database can have multiple appointments "on hold" there are duplicates in the results. I only need 1 record per patient in order to forward this information into an automated dialer to contact that patient. I do NOT want to call the patient over and over again. Once will suffice. I'm trying to make a distinction on the column 'med_rec'. One row per 'med_rec' will be awesome but I can't find a way to create a distinct on that column.
I have a table called N1
N1_no Srvarea_type_cd
1 P
1 P
2 C
2 C
2 C
3 I
Another table N2
N1_no srvarea_txt
1 ABCD
2 DEFG
3 XYZA
Can i get a query so that the data can be displayed in the following way ..
P C I
ABCD DEFG XYZA
i have downloaded ireports latest version (3.6.0) for working with jasper reports (3.5.2) and creating reports;How can I change the orientation of a 4 columns report in order to generate the columns starting from the right side.I need to generate a multiple columns report in arabic and it should be done from right to left.
View 2 Replies View RelatedCan i partition by list on multiple columns? i am trying the following code, and it is returning an error. Is there a way round this?
CREATE TABLE
(...)
PARTITION BY LIST (col1)
SUBPARTITION BY LIST (col2)
SUBPARTITION TEMPLATE (
SUBPARTITION DETAIL VALUES ('DETAIL')
SUBPARTITION ROLLUP VALUES ('ROLLUP'))
(
PARTITION RT VALUES('RT')
)
as part of an exercise we are to demonstrate a FOR LOOP from 1 - 100.
We must show the output as evidence of doing the work.
Is it possible to display the results in two columns, instead of all in one row? Because 100 rows will not fit it one screenshot!
BEGIN
FOR i in 1..100
LOOP
DBMS_OUTPUT.put_line(i);
END LOOP;
END;
/
the thread title was a bit confusing, couldn't come up with anything short to describe the question. What I am looking for is a query which will put records into groups based on matching values in one of two columns. So if two records have a matching value in column 1 or column 2 they are in the same group. See the example bellow and expected output for a "better" explanation:
--setup
CREATE TABLE foo
(foo_id NUMBER NOT NULL PRIMARY KEY,
record_number NUMBER,
record_value VARCHAR2(1));
[Code]...
--expected output
group# foo_id record_number record_value
1 1 1 A
1 2 1 B
1 3 2 B
1 4 2 C
2 5 3 D
3 6 4 E
3 7 5 E
My initial thought is that is feels a little bit like the sequential seat problem but not quite close enough. I know it could be done iteratively with PL/SQL but I am thinking there must be a way to do it in SQL I am not seeing yet.
Following is my table structure:
14:54:17 PYMTPRODIAT@UCS43 > select * from DGTEST;
ID AMOUNT DI
---------- ---------- --
1 50 D
2 50 D
3 20 D
4 60 C
2 60 C
3 20 C
now, I want the output in the following format.what could be the easiest way.. I need the output in one query,,
ID D_Net_Amount C_Net_Amount
--- ------------- --------------
1 50
2 50 60
3 20 20
4 60
I have data like :
ID NAME1 NAME2 NAME3
JJ AD MED
VI TIBO PH TIBO
I want output like
ID NAME
JJ AD
JJ MED
VI TIBO
VI PH
VI TIBO
I have a table like MyTab(a int, b int), and I am required to create a primary key index and a non-unique index on this table using columns (a,b) in a specific table space.
The back end database is Oracle 10g.
Here's what I have tried so far, needless to say, unsuccessfully.
Alter Table MyTab
Add Constraint c_1 primary key (a, b)
Using Index (Create index mytab_idx on MyTab(a, b))
Using index tablespace results_index
So my question are:
1. is this is possible? if so, what is the correct syntax.
2. assuming it is possible, using this sort of construct before? it appears to be conflicting and inconsistent to me.
my need is to perform merge - update when id column is matched, but one of others columns not.When id column is not matched then I perform insert.
It works fine for matched or not matched id column.
Commented code is my try to perform check for others columns, The code should not update when all columns match. It should update only when on of columns doesn't match (except id column of course, because it's key column).
begin
merge into copy.table1 rr
using
(
select
ID ,
DEALID ,
ESTIMATIONDATE ,
BOUNDOVERESTIMATDATE ,
ESTIMATIONTYPEID ,
MARKETAMOUNT ,
LIQUIDATINGAMOUNT ,
[code]....
My requirement is that under table name like A I have to update remaining empty columns as A and under B remaining null columns as B and so on.
View 14 Replies View RelatedI know this is a simple question for some of you, but I am new to SQLs,
I have two tables TABLE1 & TABLE2 as below, both tables contains more then 50million records:
SELECT * FROM TABLE1.
&&&&&&&&&&&&&&&&&&&&&&&&&&&
ID BUS_FID WORKID STATIONID
---------------------- ---------------------- ---------------------- ----------------------
28400000117234 245 13461428.25 16520877.8
28400000117513 403 13461428.25 16520877.8
28400000117533 423 13461428.25 16520877.8
28400000117578 468 13461428.25 16520877.8
28400000117582 472 13461428.25 16520877.8
SELECT * FROM TABLE2.
&&&&&&&&&&&&&&&&&&&&&&&&&&&
BUS_FID ID TRPELID RELPOS WORKID STATIONID
---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
114 28400000117658 28400000035396 23.225
115 28400000117659 28400000035396 23.225
116 28400000117660 28400000035396 23.225
117 28400000117661 28400000035396 23.225
118 28400000117662 28400000035396 23.225
119 28400000117663 28400000035396 23.225
120 28400000117664 28400000035396 23.225
[Code]....
Now I tried to use following SQL to update WORKID & STATIONID columns in TABLE2 but failed. BUS_FID in both tables have been UNIQUE indexed and they can be used as primary keys to join these two tables.
UPDATE (
SELECT p.WORKID px,
p.STATIONID py,
p.BUS_FID pid,
temp.WORKID tempx,
temp.STATIONID tempy,
[Code]....
with above code, Oracle returned following errors:
SQL Error: ORA-00904: "TEMPID": invalid identifier
00904. 00000 - "%s: invalid identifier"
BTW, both two tables contains over 50 million records. So, if you have a better SQL to perform the same task.
I have three tables,let's say
table stores
sid | store_name
1 | one
2 | two
3 | three
table products
pid | sid | p_name
1 | 2 | pone
2 | 2 | ptwo
3 | 3 | pthree
table sales
said | sid | pid
1 | 2 | 1
2 | 3 | 1
3 | 2 | 2
4 | 1 | 3
5 | 2 | 2
6 | 3 | 2
7 | 3 | 2
and i want display the product that sells best in every store. I try to group by multiple columns counting how many times each product was sold in every store, but don't know how to select the one which was best sold (maximal number of times)