Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2008
    Posts
    91

    Updating Table using Alias column Name In Where Clause?

    SQL> Create table TEST_NEW (STATS VARCHAR2(50) ,
    2 CODE VARCHAR2(5) ,
    3 LOC VARCHAR2(2) ,
    4 KLI VARCHAR2(5) ,
    5 TYPE VARCHAR2(2) ,
    6 PROPTION VARCHAR2(50) ,
    7 ORDER_NO VARCHAR2(50) ,
    8 PREPRO VARCHAR2(50) );

    Table created.

    SQL> Insert into TEST_NEW values('W', 'O', 'AA', '68', 'E', 'P1', '1', 'Y');

    1 row created.

    SQL> Insert into TEST_NEW values('W', 'O', 'BB', '66', 'E', 'P1', '2', 'Y');

    1 row created.

    SQL> Insert into TEST_NEW values('W', 'O', 'BB', '67', 'E', 'P1', '3', 'Y');

    1 row created.

    Select STATS , TYPE , PROPTION,ROW_NUMBER() OVER(PARTITION BY STATS, TYPE, PROPTION ORDER BY STATS) as TOSTART, ORDER_NO ,ROWNUM from SIMIS_DBA.TEST_NEW

    Having the above query with the alias name Called TOSTART.

    how to use TOSTART COlumn in Where clause when Updating.

  2. #2
    Join Date
    Jul 2004
    Posts
    17
    An analytical function can only be in the where outside of the main select, which you out in a subquery.
    You could stick all this in a subquery and use it in your update.

    UPDATE tab
    SET col = col1
    where tab1.order_no in (
    select order_no
    from (Select STATS,
    TYPE ,
    PROPTION,
    ROW_NUMBER() OVER(PARTITION BY STATS, TYPE, ROPTION ORDER BY STATS) as TOSTART,
    ORDER_NO ,
    ROWNUM
    from SIMIS_DBA.TEST_NEW
    )
    where tostart = 1)

  3. #3
    Join Date
    Jul 2008
    Posts
    91
    Result Set of TOSTART Column itself i want to use in where clause.

    update tab1 set col=col1 where TOSTART='1';

    Since TOSTART is the alias form the select query.

  4. #4
    Join Date
    Jul 2004
    Posts
    17
    Sorry, you've lost me a bit there. My use of tab and cola has unnecessarily complicated it, apologies.

    Using the correct column names, what are you hoping to update?

  5. #5
    Join Date
    Jul 2008
    Posts
    91
    Introducing a new column i.e ALIAS in select query
    Based on the ALIAS I have to update the table.

    Update test_new set STATS='R' WHERE TOSTART=1;

  6. #6
    Join Date
    Jul 2004
    Posts
    17
    UPDATE (
    select STATS
    from (Select STATS,
    TYPE ,
    PROPTION,
    ROW_NUMBER() OVER(PARTITION BY STATS, TYPE, ROPTION ORDER BY STATS) as TOSTART,
    ORDER_NO ,
    ROWNUM
    from SIMIS_DBA.TEST_NEW
    )
    where tostart = 1) T
    SET T.STATS = 'R'

    OR

    UPDATE (
    Select STATS,
    TYPE ,
    PROPTION,
    ROW_NUMBER() OVER(PARTITION BY STATS, TYPE, ROPTION ORDER BY STATS) as TOSTART,
    ORDER_NO ,
    ROWNUM
    from SIMIS_DBA.TEST_NEW
    ) T
    SET T.STATS = 'R'
    WHERE T.TOSTART = 1

  7. #7
    Join Date
    Jul 2008
    Posts
    91
    First query giving the error

    ORA-01732: data manipulation operation not legal on this view

    second :

    ORA-01733: virtual column not allowed here

    Calculated columns are not allowing to Update.

  8. #8
    Join Date
    Jul 2004
    Posts
    17
    UPDATE simis_dba.test_new
    SET T.STATS = 'R'
    WHERE order_no =
    (
    select order_no
    from (Select STATS,
    TYPE ,
    PROPTION,
    ROW_NUMBER() OVER(PARTITION BY STATS, TYPE, ROPTION ORDER BY STATS) as TOSTART,
    ORDER_NO ,
    ROWNUM
    from SIMIS_DBA.TEST_NEW
    )
    where tostart = 1) T


    Try him, if that doesn't work I'm not sure where to go next.
    This is so much easier in sqlserver and teradata.

    Hope this works for you

Posting Permissions

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