CODEWITHSUNDEEP
mysqlsqldatabase
user2986042
user2986042

Reputation: 1270

Error getting with the target table of the UPDATE is not updatable

I am running a simple MySQL query to update with sub-query . This is the query what i wrote 

UPDATE (
         SELECT IP2.ID, IP2.IP4, IP2.CLIENT FROM IP_ADDRESS IP2 WHERE IP2.V_NET = VNET_ID AND IP2.CLIENT IS NULL AND IP2.IP4 < (RANG_E - 1) 
         ORDER BY IP2.IP4, IP2.CLIENT LIMIT 1
         )AS IP SET IP.CLIENT = C_ID;

after running i am getting following error :

The target table IP2 of the UPDATE is not updatable

I have got some related question mysql - The target table of the UPDATE is not updatable and The target table of the UPDATE is not updatable but i didn't get a clear answer .

Anything i am missing here ? how can i write this query ? Any help ?

Upvotes: 0

Views: 3157

Answers (2)

Sam M
Sam M

Reputation: 4166

The format of an UPDATE statement is as follows:

UPDATE [table name]
   SET [column name] = [new value]
(optional WHERE clauses)

The update sql you wrote in your question makes no sense. Rewrite it using the correct structure.

Upvotes: 1

dani herrera
dani herrera

Reputation: 51685

If you have primary key on your table you are safe.

Take a look to this dirty trip:

Try it at SQL Fiddle

sample table:

create table t( a char(1), i int );

insert into t values 
('a', 1),
('b', 2),
('c', 3),
('d', 4);

Updating through pk

/* updating using double subquery, only way to use your same table */

update t
set a = 'z'
where i = (select i from (select * from t) t2  order by i desc limit 1);

Apply it tou your environment.

Upvotes: 1

Related Questions