Error Cannot Modify Table Or View Used In Subquery
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up SQL Error 360: Cannot modify table or view used in subquery up vote 0 down vote favorite 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. sql share|improve this question edited Apr 28 at 6:16 marc_s 452k938641029 asked May 26 '11 at 9:39 Dec2010 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. –Lieven Keersmaekers May 26 '11 at 10:15 add a comment| 1 Answer 1 active oldest votes up vote 0 down vote 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) share|improve this answer edited Jun 1 '11 at 11:39 answered May 26 '11 at 10:09 Diego Scaravaggi 1715 ORACLE Informix –Dec2010 May 26 '11
Because of the danger of entering an endless loop, this action is not allowed, except in the case of an uncorrelated subquery in the WHERE clause of the DELETE or UPDATE statement.
To avoid this error, first select http://stackoverflow.com/questions/6136538/sql-error-360-cannot-modify-table-or-view-used-in-subquery the input data into a temporary table, and then refer to the temporary table in a separate DELETE, UPDATE, or INSERT statement. A subquery with a correlated column name cannot reference in its FROM clause the same table that an enclosing UPDATE http://www.oninit.com/errorcode/index.php?pageid=-360 or DELETE statement is modifying, as in the following example: database stores_demo; update orders set ship_charge = ship_charge + 2.00 where customer_num in (select new_order.customer_num from orders as new_order where orders.ship_weight < new_order.ship_weight); In an SPL routine, even an uncorrelated subquery is not allowed in a DELETE or UPDATE statement that modifies the same table that the FROM clause of the subquery specifies, as in the next example: database stores_demo; create procedure ship_count(customer_id integer) returning integer define shipcount integer; select count(*) into shipcount from orders where customer_id = orders.customer_num and ship_charge > 20; return shipcount; end procedure; update orders set ship_charge = ship_charge + 2.00 where customer_num in (select customer_num from customer where customer.state = 'CA' and ship_count(customer.customer_num) > 5); Copyright Privacycheck out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. http://www.dbforums.com/showthread.php?1214588-Update-using-a-join Results 1 to 5 of 5 Thread: Update using a join Tweet Thread Tools Show Printable Version Subscribe to this Thread… Search Thread Advanced Search Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 03-26-06,07:44 #1 exterminator View Profile View Forum Posts Registered User Join Date Jan 2006 Posts 38 Unanswered: Update using a join I want to do something like this on an informix dynamic server 9.3 table.. the idea is using a join for error cannot update Code: UPDATE table_1 SET table_1.column_1 = 'some value', table_1.column_2 = 'some other value' FROM table_1, table_2 WHERE table_1.column_2 = 'something' AND table_1.column_4 = table_2.column_1 AND table_1.column_5 = table_2.column_2 AND table_1.column_6 BETWEEN table_2.column_4 AND table_2.column_4; -- Here table_1.column_6, table_2.column_4 AND table_2.column_4 are date columns But it does not work. It errors out saying "Syntax error". Can anyone suggest an alternative to do this? Thanks for taking a look. Best regards. Reply With Quote 03-27-06,06:52 #2 gurey View Profile View error cannot modify Forum Posts Registered User Join Date Aug 2003 Location Argentina Posts 780 Hi, Please check sintax of UPDATE, i think that clausule FROM in UPDATE not use. UPDATE tablename SET colum=value,.... WHERE..... NOT FROM Gustavo. Reply With Quote 03-27-06,09:37 #3 mjldba View Profile View Forum Posts Registered User Join Date Dec 2003 Location North America Posts 146 Agree with Gustavo on the syntax comment. The only place you're going to use a FROM statement when doing an UPDATE is if you're doing a subquery. UPDATE table1 set column 1 = (SELECT column1 FROM table2 WHERE table1.column1 = table2.column1 and table2.column2 = some_condition and blah-blah-blah ..... ) Reply With Quote 03-28-06,06:10 #4 exterminator View Profile View Forum Posts Registered User Join Date Jan 2006 Posts 38 Yeah, I agree the FROM looks wierd but in the Informix SQL guide (syntax?) they suggested this. And it is supposed to work on Parallel server. I am working with Dynamic server 9.3 and hence this was not supposed to work but I just gave a try. But I think I was not clear with what I wanted to do. I do not want to update a field using a value from second table. What I want to do is - I only need to update the rows in table 1 that have a match for the combination of the conditions as below with some known value (kind of a co