pradyu pradyu - 1 year ago 71
SQL Question

You can't specify target table 'tablename' for update in FROM clause

got this exception while using one of the answers from stackoverflow. this is my query and am using JSP to pass queries:

String queryString = "INSERT INTO tablename(SL_No,candidate,phone,pan,mailid) VALUES(SELECT (MAX(SL_No)+1 newSL_No from tablename), ?, ?, ?, ? ))";

Thanks in advance.

Answer Source
INSERT INTO tablename(SL_No,candidate,phone,pan,mailid) 
select MAX(SL_No) + 1, ?, ?, ?, ?
from tablename

But actually it looks like you could just use the auto-increment of the SQL engine to do MAX(SL_No) + 1. If you change to column to that then your statement would be

INSERT INTO tablename(candidate,phone,pan,mailid) 
values (?, ?, ?, ?)