Geek asks and answers

How to get a subquery that will be updated for each row inserted in mysql?

My query looks like this:

INSERT INTO table1 (lfd, somedata)
select (select max(lfd) + 1 from table1), somedata from table2

Table 2 has several rows. So I want some rows to be inserted into table1. This works, but lfd is set to the same number for all inserted lines.

So, if max (ldf) + 1 is “2” for the first row inserted, it is also “2” for all next lines. This is not true because max (lfd) + 1 for the next line should return "3", of course.

How to tell mysql to re-evaluate a subquery for each insert?

I cannot touch table structures because I am transferring data from one php gallery to another another php gallery. The table structure is under application control.

In Oracle, I would just define the sequence and highlight squeencename.nextval in the subquery. - I'm sure this will work, but mysql does not offer a sequence, as far as I know, right?

+3
source share
2 answers

Try it once:

SELECT MAX(lfd) + 1 INTO @i FROM table1;

INSERT INTO table1 (lfd,col1,...)
SELECT @i:=@i+1,somedata,... FROM SomeTable;

Essentially, this simply uses the variable as a sequence, initializing it with the current maximum id from the table.

Please note that concurrency problems can occur here if rows are inserted in table1during the execution of this request.

0
source

Just define it lfdas an auto-increment column . Then you do not have to worry about select max(...) ....

Here's another way to do this:

insert into table1 (lfd, somedata)
select 
  (
    select count(*) + (select count(*) from table1) 
    from table2 l
    where l.lfd <= r.lfd
  ),
  r.somedata
from table2 r;

How it works:

  • table2 1 n ( n - table2)
  • max(lfd) ,

: n ** 2, , , .

0

More articles:


All Articles