1

I have 2 tables referenced one to another:

CREATE TABLE a (
    AID INTEGER PRIMARY KEY
) ;
/
CREATE TABLE B (
    PO INTEGER,
    BID INTEGER PRIMARY KEY,
    ALINK INTEGER 
) ;
/
ALTER TABLE B ADD 
FOREIGN KEY (ALINK) REFERENCES A (AID)
/

I need PL/SQL procedure that will update some rows in table B depending on referenced row count in A. I've tried something like counting rows, but I'm not sure it is this the best way:

EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM 
    (SELECT * FROM A WHERE AID = 
        (SELECT ALINK FROM B WHERE ROWNUM = K))' 
INTO TMP;
ARTEMYO
  • 13
  • 2
  • 2
    Please show example input and output data. You don't need to use execute immediate. – OldProgrammer Aug 15 '15 at 14:19
  • Unrelated but: you don't need the `;` ***and*** the `/` http://stackoverflow.com/questions/1079949/when-do-i-need-to-use-a-semicolon-vs-a-slash-in-oracle-sql/10207695#10207695 –  Aug 15 '15 at 17:45
  • `SELECT ALINK FROM B WHERE ROWNUM = K` will only return rows if `K=1` if `K` is anything else then it will return zero rows. – MT0 Aug 15 '15 at 22:54
  • If `ALINK` is not `NULL` then there will always be exactly one matching row in table `a` (since there is a foreign key relationship to a primary key) - what do you expect to have to count? – MT0 Aug 15 '15 at 22:58

0 Answers0