CODEWITHSUNDEEP
sqloracle-databaseplsqlsql-updatesql-returning
Jared
Jared

Reputation: 115

How do I select the row which I modified in Oracle SQL

I want to update a row after which I want to retrieve the entire row in Oracle SQL. The table and corresponding query I'm using are shown below. I want to avoid querying the DB twice. Hence, I'm doing a select after the update, but I'm running into the error shown after the query. Any idea how to fix this?

CREATE TABLE mytable (
    sid NUMBER NOT NULL,
    eid VARCHAR(18) NOT NULL,
    first TIMESTAMP,
    updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created TIMESTAMP,
    status VARCHAR(30),
    tid VARCHAR(18),
    wid VARCHAR(18),
    CONSTRAINT mytable_pk PRIMARY KEY (sid, eid)
    ENABLE
);

BEGIN
UPDATE mytable SET wid=?, status=? WHERE sid=?;
SELECT * FROM mytable WHERE sid=?;
END;

ERROR:

java.sql.SQLException
ORA-06550: line 3, column 7:
PLS-00428: an INTO clause is expected in this SELECT statement

Corresponding Java Snippet:

PreparedStatement statement = prepareStatement(sql);
int index = 0;
statement.setString(index++, newWid);
statement.setString(index++, newStatus);
statement.setInt(index++, sid);
statement.setInt(index++, sid);
ResultSet results = statement.executeQuery();
if (results.next()) {
    Record rec = new Record();
    fill(rec, results);
}
return record;

Upvotes: 0

Views: 387

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65278

You can use returning into clause as in the following :

SQL> set serveroutput on;
SQL> DECLARE
  v_sid      mytable.sid%type;
  v_eid      mytable.eid%type;
  v_first    mytable.first%type;
  v_updated  mytable.updated%type;    
  v_created  mytable.created%type;
  v_status   mytable.status%type;
  v_tid      mytable.tid%type;
  v_wid      mytable.wid%type;        
BEGIN
  UPDATE mytable 
     SET wid=v_wid, status=v_status
   WHERE sid = v_sid
   RETURNING sid, eid, first, updated,
             created, status, tid, wid 
        INTO v_sid, v_eid, v_first, v_updated,
             v_created, v_status, v_tid, v_wid;
   dbms_output.put_line(' sid :' ||v_sid);  
   dbms_output.put_line(' eid :' ||v_eid);  
   dbms_output.put_line(' first :' ||v_first);  
   dbms_output.put_line(' updated :' ||v_updated);      
   dbms_output.put_line(' created :' ||v_created);  
   dbms_output.put_line(' status :' ||v_status);  
   dbms_output.put_line(' tid :' ||v_tid);  
   dbms_output.put_line(' wid :' ||v_wid);    
END;

Upvotes: 2

Alexey Shmelev
Alexey Shmelev

Reputation: 1

In an anonymous PL/SQL block you can do data modification using UDPATE, but to select data you need to select it into something. A variable with row type corresponding to the data you're selecting, an explicit or implicit cursor or something like that. Example:

BEGIN
   UPDATE mytable SET wid=?, status=? WHERE sid=?;
   FOR R in (SELECT * FROM mytable WHERE sid=?) LOOP
       DMBS_OUTPUT.PUT_LINE(r.sid ||' ' || r.wid);
       ... do something here
   END LOOP;
END:

Upvotes: 0

Related Questions