SQL & PL/SQL :: Select Updated Rows From One Table

Jul 13, 2013

There are two tables like I posted below.I want a SQL query which selects all the rows from TABLE A which are not present in TABLE B. Also the select statement should pick all those rows which has updated value of COL_A2 in TABLE A.

TABLE A
COL_A1COL_A2

AAAMOBILE
BBBTABLET
CCCDESKTOP
DDDUNKNOWN

TABLE B
COL_B1COL_B2

AAAMOBILE
BBBUNKNOWN
CCCDESKTOP

The select statement should return following rows from TABLE A

COL_A1COL_A2
BBBTABLET
DDDUNKNOWN

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Rows Of A Table Updated

Jul 17, 2012

write a query find which rows of a table is updated on 2 days before?

(OR)

In table there r so many rows write a query which two rows r updated last two day before?

View 4 Replies View Related

SQL & PL/SQL :: Can Have All Rows In A Table Updated At Once In Merge Statement

Aug 10, 2010

Can I have all the rows in a table updated at once in the merge statement?

MERGE INTO providermaster a
using
(
SELECT * FROM PROVIDERMASTER@INGEST) b
ON (b.MASTERPROVIDERID=a.MASTERPROVIDERID)
WHEN MATCHED THEN

UPDATE ....... I want to update all the other rows at once..

View 24 Replies View Related

SQL & PL/SQL :: How To Select Latest Updated Record From Table

Sep 21, 2011

I want to know like How we can select the latest updated record from xyz table. that record has STATUS column. I also want to check if the status is RED or GREEN query should return if the status is red then 1 and if the status is GREEN then it should return 0

View 8 Replies View Related

SQL & PL/SQL :: Returning Updated Rows With REF CURSOR?

Sep 27, 2013

The following code is indicative of what I'd like to do (as in not correct at all ). Would there be a more immediate way to accomplish this other than executing a SELECT statement after the UPDATE?

-- Incorrect indicative example 1.

DECLARE
v_cur SYS_REFCURSOR;
BEGIN
UPDATE table1(f1, f2)
SET ('v1', 'v2')
WHERE f3 = 'v3'
RETURNING <updated_rows> INTO v_cur
END;

-- Incorrect indicative example 2.

DECLARE
v_cur SYS_REFCURSOR;
BEGIN
OPEN v_cur FOR
UPDATE table1(f1, f2)
SET ('v1', 'v2')
WHERE f3 = 'v3'
END;

View 4 Replies View Related

How To Select 10 Rows From Mission Table

Sep 2, 2008

i want the 10 most recent mission date from mission table . i have tried using rownum but could not get it. TOP does not work

select * from
(select length(code_name) leng from missions
where length(code_name) >7
order by mission_date)
where rownum = 10
;

this does not work ::: no rows are selected and i do rownum = 1 then gives me one record and when i do rownum>=1 gives me all records

View 10 Replies View Related

SQL & PL/SQL :: Select Rows From Table With Same Particular Field?

Aug 20, 2012

select the rows from a table with the same particular field in PL/SQL. Actually I don't want to write two loops one inserted into another.

View 7 Replies View Related

To Select Recent Row Updated Detail

May 26, 2011

I have one table,in that table have number of rows. I want to select recent row updated details.

View 2 Replies View Related

Select Query With Four Table Its Generating Around 650 Rows?

Feb 20, 2013

i have a select query with four table its generating around 650 rows. and i need to update these 650 rows only.

for example

update ps_po_lining b
set y.recv_req = 'N'
where recv_req in

[Code]....

this query runs but its updating 6000 rows. which is not right. we need to update what ever the select query is retrieving.

View 5 Replies View Related

Table Has No Rows - Select Taking Time

Aug 23, 2010

We were trying to insert approx 76 million records worth of approx 4 GB in a table when the insert operation failed with the archive log error.

The database was hanged, we re-started the database and currently there are no records in the table but when we are firing a select

select count(*)
from table

It's taking approx 2 mins to come out with the result.

We checked and found that there are no locks on the table currently.

what do we need to do to get the performance back

View 5 Replies View Related

SQL & PL/SQL :: Block To Select 2 Rows From Table And Execute Them

Aug 3, 2010

I have a table with name SQL_STATEMENTS and it has 1 column, Column name is STATEMENT_DESC

The contents of rows in Table above are:

select * from emp;
select * from dept;

Can I write a PLSQL block that can select these 2 rows from the table and execute them?

View 9 Replies View Related

How To Store Total No Of Updated Rows (number) In A Variable

Jun 21, 2011

how to store total no of updated rows (number) in a variable after executing an updation query using script

View 2 Replies View Related

SQL & PL/SQL :: How To View Rows In Table Format Using Select Query

May 28, 2012

I need to view the rows of the result of a select query in table format in GUI application.I have used XMLELEMENT to somewhat match the scenario to display as ','(comma) separate values as b belwo

SELECT RTRIM (XMLAGG (XMLELEMENT (e, EMPNO || ',')).EXTRACT ('//text()'),
',')
AS empid,
RTRIM (XMLAGG (XMLELEMENT (e, ENAME || ',')).EXTRACT ('//text()'),

[code]...

But the case is I need to display the value in table format Horizontally as below

EMPIDemployee nameDEPID
778
278CLARK10
397MILLER
934KING

[code]...

View 14 Replies View Related

SQL & PL/SQL :: How To Find Which Table Is Last Updated

Apr 11, 2012

I want to know that How to find which table got last updated and how to find last DDL and DML operation obtained in which table? here I know the table name

SQL> SELECT LAST_DDL_TIME FROM DBA_OBJECTS WHERE OBJECT_NAME='PREM';

LAST_DDL_
---------
20-MAR-12

SQL> TRUNCATE TABLE PREM;

Table truncated.

SQL> SELECT LAST_DDL_TIME FROM DBA_OBJECTS WHERE OBJECT_NAME='PREM';

LAST_DDL_
---------
10-APR-12

Note: With out enable the auditing I want to know that .

View 6 Replies View Related

Forms :: Details Should Be Updated From One To Other Table

Mar 19, 2011

when the tables are updated, the following detals must be correct to ensure that the links in the affected tables are in place.

PLUPDATE_NEW(PLUP_SAVE_SEQ field value) must be the same with PLUPDATE_BENEF_NEW (PLUP_NEW_BENEF_SAVE_SEQ field value)
PLUPDATE_OLD(PLUP_SAVE_SEQ field value) must be the same with PLUPDATE_BENEF_OLD (PLUP_OLD_BENEF_SAVE_SEQ field value)?

[Code]....

i tried this code, what should i do in the link for this tables?

View 5 Replies View Related

SQL & PL/SQL :: Data Should Be Updated Back To Table

Aug 16, 2011

The requirement is, the combination of col1,col2,col3 and col4 should always be unique, and wherever the col1, col2,col3 are same then col4 should be the sequence, starting from 1. Likewise the data should be updated back to the table.I'm able to do this using PL/SQL. Can I do the same using a single update statement?

create table tab1 (col1 number(5), col2 number(5), col3 number(5), col4 number(5));

Existing Data:

insert into tab1 values (101,521,3,1);
insert into tab1 values (101,521,3,1);
insert into tab1 values (101,522,3,2);
insert into tab1 values (101,522,3,2);
insert into tab1 values (101,523,3,1);
insert into tab1 values (101,523,3,2);
[code]....

View 3 Replies View Related

Transaction To Be Updated To Master Table

Feb 16, 2012

I have approximately 1200 transaction to be updated to a master table. There are other columns in the master table but only one column is being updated. I would like to use sqlloader if possible or any other efficient means. Those 1200 record is stored in an excel spreadsheet. The col1 of the excel spreadsheet have to match col1 of the master table inorder for update col2 from the excel spreadsheet. Here is an example of the data. My operation system is HPUX and database is Oracle 10g.

Master table
col1 col2 col3 col 4
4238 susan 56e
5879 h698c rich 12g
7091 joyce 34b
0876 mike 25n
7501 k956b robert 87c
9498 angela 67r
3645 doris 92y

excel spreadsheet

col1 col2
9498 a784r
3645 a784r
4238 a784r
7091 a784r
0876 a784r

View 2 Replies View Related

PL/SQL :: Inserting Data Into Updated Table

Jul 22, 2012

I have a table created with the following code:

CREATE TABLE CLIENT
(
CLIENT_ID NUMERIC(2),
CLIENT_NAME VARCHAR2(25),
CONTACT_LAST_NAME VARCHAR2(15),

[Code]...

I have altered the table to have the following:

ALTER TABLE CLIENT
ADD CLIENT_CITY VARCHAR2(25);

I am trying to insert new data into said table that was updated:

INSERT INTO CLIENT
(CLIENT_CITY)
VALUES
('Mount Pearl')
WHERE CLIENT_ID = 1;

Then I get the following error:

Error starting at line 1 in command:

INSERT INTO CLIENT
(CLIENT_CITY)
VALUES
('Mount Pearl')
WHERE CLIENT_ID = 1
Error at Command Line:4 Column:15
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:   
*Action:

View 3 Replies View Related

Server Administration :: How To Find Updated Table

Mar 9, 2012

how to find a table is updated and when the table is updated.

View 1 Replies View Related

SQL & PL/SQL :: Every Record In Temp Table Getting Updated With LAST In Datafile?

Nov 24, 2010

is that my looping script updates EVERY record with the LAST record of the datafile. where it's doing this..

DECLARE....
CURSORS...
-- *****************************************************
-- C 1
-- *****************************************************
CURSOR C1

[code]...

View 19 Replies View Related

Server Administration :: How To Know Which Table In Oracle Updated / Modified

Jul 29, 2010

I use oracle 9i. After doing execution ,process, how can I know which table in the oracle have been update/modify?

View 18 Replies View Related

Forms :: Save Record In Table Before Saving Updated Version?

Apr 6, 2013

I have a multi record block . I need to implement a functionality where the user updates a field in the record but the form saves the previous version of the record in another table ..

View 3 Replies View Related

SQL & PL/SQL :: Dynamic Update Of Cursor Records When Table Gets Updated Immediately

Apr 16, 2010

I am having a table with 4 columns as mentioned below

For a particular prod the value greater less than 5 should be rounded to 5 and value greater than 5 should be rounded to 10. And the rounded quantity should be adjusted with in a product starting with order by of rank with in a prod else leave it

Table1

Col1prodvalue1rank
1A21
2A62
3A53
4B61
5B32
6B73
7C41
8C22
9C13
10C74

Output

Col1prodvalue1rank
1A51
2A52
3A33
4B101
5B02
6B63
7C51
8C52
9C03
10C44

I have taken all the records in to a cursor. Once after rounding the request of 1st rank and adjusting the values of next rank is done. Trying to round the value for 2nd rank as done for 1st rank. Its not taking the recently updated value(i,e adjusted value in rounding of 1st rank).

This is because of using a cursor having a value which is of old value. Is there any way to handle such scenario's where cursor records gets dynamically updated when a table record is updated.

View 9 Replies View Related

PL/SQL :: Select 4 Rows And Then Next 4 Rows?

May 27, 2013

in my sql we can use limit to select first 4 rows in the table then next 4 rows ,can oracle do that ?

I have fifty rows inside the table

select * from student where rownum between 0 and 4 order by stuid ->it work

select * from student where rownum between 5 and 9 order by stuid -> did not work

what is the correct way to do it ?

View 10 Replies View Related

SQL & PL/SQL :: Rows Not In Inner Select?

Apr 11, 2013

I am trying to find records in table A that don't have any corresponding records in table B

select a.ID,a.templatenames from cbe a where not exists (select 1 from cbe_child b where a.ID=b.cbe_id)

Both tables have about 100 mil rows. And there is no Parent/Child relationship here, A.ID is a PK column.how can i write this select to perform better.

View 2 Replies View Related

How To Select Rows From Two Tables

Aug 30, 2007

Consider the following tables

MANAGERID
101
102
103
104

MANAGERID EMPID
101 ----------------24
101-----------------25
101 ----------------26
104 --------------- 27

write sql query to get the following output without using minus,union,intersect.

MANAGERID EMPID
101 ---------------------------------- 24
101------------------------------------25
101 -----------------------------------26
102 -----------------------------------N/A
103 -----------------------------------N/A
104 -----------------------------------27

View 3 Replies View Related

SQL & PL/SQL :: How To Select Last 10 Days Rows

Aug 2, 2012

My Table structure is

SL# Start_ts
1 7/31/2012 3:53:42.864070 AM
2 7/26/2012 2:13:58.280928 PM
3 7/25/2012 2:13:41.692569 PM
4 7/21/2012 2:13:26.821070 PM
5 7/18/2012 2:13:07.827278 PM
6 7/13/2012 2:05:35.023520 PM

Question

How to select last 10 days rows only (from sysdate)

Error

1) SQL> select * from Test where to_date(start_ts, 'DD-MON-YYYY HH24:MI:SS') > to_date(sysdate, 'DD-MON-YYYY HH24:MI:SS')-10 ; (or)

2) SQL> select * from Test where to_date(start_ts, 'MM/DD/YYYY HH24:MI:SS') > to_date(sysdate, 'MM/DD/YYYY HH24:MI:SS')-10 ;
ORA-01858: a non-numeric character was found where a numeric was expected

[Code]...

View 5 Replies View Related

SQL & PL/SQL :: Select Rows With Max Condition

Aug 13, 2010

I have been trying to find out how to write the following query:

Suppose the following table:

ID seq
24 1
24 2
24 3
67 1
67 2
67 3
67 4
67 5
13 1
13 2

I would like to retrieve the rows for every different ID with its max value. For one ID that would be:

select * from TABLE where seq in(
select max(seq) from TABLE where id=24)

How can i do this for all the rows??

View 2 Replies View Related

Select All Rows And Order By Timestamp

Dec 27, 2006

I have 3 tables that I want to select all rows from and then order by the timestamp of when the row was inserted.

View 7 Replies View Related

50000 Rows Only From Select - How To Increase

Jun 1, 2012

I would like to know how to increase the result set of a 'Select' statement? I did a 'Select' that should have returned 36,000 rows and got only 5000 rows. What access level do I need to change this and what do I need to change? I am trying to do a migration of data from a delimited file to a table in Oracle. Yet, the data has to be filtered out prior to loading to the table?

SQLSELECT MIN(major_zipcode)
FROM TEMP WHERE MAJOR_CITIES IN (select distinct major_cities from temp);

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved