Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2003
    Posts
    218

    Unanswered: create a delete script with a quoted literal (was "simple question")

    Oracle9i on WinXP:

    I am trying to create a delete script which will delete all rows based on certain condition. Now the query output is as shown below...

    select 'delete from table ' || table_name || 'where id >= 1%;'
    from all_tables
    where owner = 'DEMO';

    Output
    delete from table ACCOUNTwhere id >= 1%;

    But, I need it in this way..
    delete from table ACCOUNTwhere id >= '1%';

    any inputs greatly appreciated.

    thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select 'delete from table ' || table_name || ' where id >= ''1%'';'
    notice i added a space after the table name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2003
    Posts
    218
    I had tried that option.. but it comes up with an error message ORA00923 error FROM keyword not found where expected...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh man, i cannot believe i didn't spot this the first time through

    remove the word "table"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2003
    Posts
    218
    sorry that was a mistake from my side.. but the issue still exists.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    select 'delete from ' || table_name || ' where id >= ''1%'';'
    from all_tab_columns
    where owner = 'DEMO'
    and column_name = 'ID';
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Dec 2006
    Posts
    5
    select 'delete from table ' || table_name || ' where id >= ''1%;'''
    from all_tables
    where owner = 'DEMO';

    Bob

  8. #8
    Join Date
    Dec 2006
    Posts
    5
    Oops - got my ; in the wrong place - beilstwh got it though
    Bob

  9. #9
    Join Date
    Sep 2003
    Posts
    218
    Great!! it works. Many thanks - beilstwh..

  10. #10
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    You can lose the optional "FROM" keyword as well. Beats me why everyone bothers to type it.

    btw, what does it mean for ID to be greater than '1%'? Is that intended to exclude '1$' but include '1&'?

  11. #11
    Join Date
    May 2004
    Posts
    95
    Quote Originally Posted by WilliamR
    You can lose the optional "FROM" keyword as well. Beats me why everyone bothers to type it.

    btw, what does it mean for ID to be greater than '1%'? Is that intended to exclude '1$' but include '1&'?

    The '%' is a wildcard indicating any combination of characters, putting it after the 1 indicates that he wants to delete every ID started with 1 (ex. 1, 10, 143, etc).

  12. #12
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by xixo
    The '%' is a wildcard indicating any combination of characters, putting it after the 1 indicates that he wants to delete every ID started with 1 (ex. 1, 10, 143, etc).
    '%' is only a wildcard when used in a LIKE expression. In any other case such as '>=' it is a normal character with no special meaning. But '10' and '1a' are already >= '1' so a wildcard would be unnecessary anyway even if it was supported.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •