Re: tricky SQL SELECT - help!

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 1 Feb 2003 11:03:09 GMT
Message-ID: <b1g9hd$1288p9$2@ID-82536.news.dfncis.de>

>
> Can you imagine trying to do an update or delete?
>
> Daniel Morgan
 

Here's an attempt for a deletion. I gave up to implement the deletion as well when I realized that I have to test the various special cases for the deletion.

CREATE TABLE people(ID NUMBER, NAME VARCHAR2(50));

CREATE TABLE pets (pet VARCHAR2(20), people_ids VARCHAR2(100));

insert into people values (1, 'John');
insert into people values (2, 'Jane');
insert into people values (3, 'Fred');
insert into people values (4, 'Wilma');
insert into people values (11, 'Dick');
insert into people values (201, 'Mike');
insert into people values (311, 'Tom the cabin boy');
insert into people values (1410239, 'Uncle Tom Cobbley And All');
insert into pets   values ('Cat',';2;4;201;');
insert into pets   values ('Dog',';1;3;311;');
insert into pets   values ('Snake',';1;2;3;4;1410239;');
insert into pets   values ('Mouse',';4;1410239;');
insert into pets   values ('Rat',';11;201;1;4;');

COMMIT; prompt
prompt what pet does John own
prompt

SELECT pet FROM pets
WHERE people_ids LIKE (SELECT '%;'||ID||';%'

                       FROM people
                       WHERE NAME = 'John');

prompt
prompt who owns a rat
prompt

SELECT
  NAME
FROM
  people
WHERE
  instr(
    (SELECT people_ids

      FROM pets 
      WHERE 
       pet = 'Rat'),';'||TO_CHAR(ID)||';') >0;
       

prompt
prompt Mike does not own a snake anymore prompt

update
  pets
set
  people_ids = substr(people_ids,1,

                  instr(people_ids,
                     (select id from people where name = 'Mike')
                  )-2) || 
                substr(people_ids, instr(people_ids,
                     (select id from people where name = 'Mike')
                  ) + length ((select id from people where name = 'Mike')
                  ))

where
  pet = 'Rat';

select * from pets;

prompt
prompt what pet does Mike own
prompt

SELECT pet FROM pets
WHERE people_ids LIKE (SELECT '%;'||ID||';%'

                       FROM people
                       WHERE NAME = 'Mike');

DROP TABLE people;

DROP TABLE pets;

Rene Nyffenegger

-- 
  no sig today

Received on Sat Feb 01 2003 - 05:03:09 CST