CODEWITHSUNDEEP
mysqlsql
Wardiam
Wardiam

Reputation: 197

MySQL error: 'Table is specified twice both as a target for INSERT and as separate source of data'

I run this query but it gives me error just like in the title:

[PROBLEM 1]

INSERT INTO Clients(IdClient, Entity)
VALUES((SELECT max(IdClient) + 1 FROM Clients), '--New--')

I have tried to solve it with a subquery like that:

INSERT INTO Clients(IdClient, Entity)
VALUES
    (
        (SELECT maxClient FROM
        (SELECT max(IdClient) + 1 maxClient FROM Clients)) subquery,
    '--New--' 
    )

but it doesn't work. Could anybody help me, please?

UPDATE

[PROBLEM 1 SOLUTION]

Finally I tested the VirCom query and it worked but including the specific column names:

INSERT INTO Clients(IdClient, Entity)
SELECT max(IdClient) + 1, '--New--' FROM Clients

[NEW PROBLEM 2]

But I have a new problem, if I run this query with other "subquery", how could I do it the query, please?. This is an example

INSERT INTO Clients(IdClient, Entity, Country)
SELECT max(IdClient) + 1, '--New--' FROM Clients,
SELECT IdCountry FROM Country WHERE Country = 'SPAIN'

Could anybody help me, please?

Thank you very much.

Wardiam

Upvotes: 0

Views: 818

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

Don't do it! Define an auto-increment column in the table and let the database handle this for you:

create table clients (
    idclient int auto_increment primar key,
    entity ?,
    . . .
);

Then, just leave it out so a new value appears:

insert into clients (Entity)
    values ('--New--');

Your version is subject to race conditions (where different threads end up with the same maximum value). It could also require scanning the entire table.

Upvotes: 1

VirCom
VirCom

Reputation: 3612

I tested that with query:

INSERT INTO Clients SELECT MAX(IdClient) + 1, '--New--' FROM Clients;

And it works )

Upvotes: 1

Related Questions