0

I got the error

Cannot modify table or view used in subquery

while executing this query.

Here raw_str in my_table_view has reference to raw_str in my_table.

Please suggest how to update the 'flag'.

update my_table
set flag = 'X'
where raw_str = (
                select raw_str from my_table_view
                where a_num in 
                    (
                        select num_id from num_table
                        where state not in (0, 1, 7, 99, 10, 11, 20, 21)
                        )
                )

Thanks in advance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dec2010
  • 1
  • 1
  • 1
    What SQL dialect? For MS SQL servers, such opertaion should be allowed; for MS Access for example not. – Arvo May 26 '11 at 10:06
  • possible duplicate of [Error 360: Cannot modify table or view used in subquery.](http://stackoverflow.com/questions/3083609/error-360-cannot-modify-table-or-view-used-in-subquery) – Lieven Keersmaekers May 26 '11 at 10:15

1 Answers1

0

correct answer is dbms dependent, using MS-SQL 2000 2005 2008 and sybase you may use powerful UPDATE FROM with special clause WITH(NOLOCK) in order to force derived table "my_table_view" to be reused.

For other dbms (ORACLE Informix) you have to load subquery into temp

which is your platform ?

Ciao Diego

Sql server 2005 2008 update from:

UPDATE B1
SET
    B1.PARAMETER_DS = 'UPDATED'
FROM
  B_PARAMETERS B1 WITH(ROWLOCK)
INNER JOIN
  (SELECT B2.* FROM 
 B_PARAMETERS B2 WITH(NOLOCK)
WHERE FAMILY_ID = 5 ) B2
ON
    B1.PARAMETER_PK = B2.PARAMETER_PK
WHERE
    B2.PARAMETER_VL = 'X';

For Oracle: Create Global Temporary Table How to create a temporary table in Oracle For Informix Create Temp Table: Insert into temp values (select.... order by id)

Community
  • 1
  • 1
Diego Scaravaggi
  • 363
  • 2
  • 13
  • It seems that recent version of Informix IDS support **update from** behavior. In any case load into temp table is works alse on old stsndard engines. – Diego Scaravaggi Jun 01 '11 at 11:00