1

Topic: a variable in UPDATE

All greetings. And all I congratulate on holidays. Now to business.
There are two tables:
Tab1 With fields: id1, nom
Tab2 With fields: id1, nom
It is necessary in Tab1 to find records corresponding id1 in Tab2, among them to find the maximum value nom and to interpose it into an appropriate line of table Tab2. And in each following record value nom should
The one who read up already to this place, the surprise in the form of remarkable, undoubtedly putting all on the places of an example waits

UPDATE Tab2, Tab1
SET Tab2.nom = (SELECT MAX (nom) FROM Tab1 WHERE Tab2.id1 = Tab1.id1) +1
WHERE Tab1.id1 = Tab2.id1;

My request everything that is necessary does, but not  nom. Simply, stupidly increases it on 1. That is even finding for example three records and Max (nom) =10, the request puts simply down in the field nom 11,11,11, and I want that was 11,12,13. I do not hope At all that clearly explained, but very much tried. Help friends, to solve the task.

2

Re: a variable in UPDATE

2 wrote:

a surprise in the form of remarkable, undoubtedly putting all on the places of an example

Alas, the fig it into place does not put.
Show better an example dataful - an initial state and ending state.
Well or as reflection from  - try here such :

UPDATE Tab2, Tab1, (SELECT @temp: = MAX (nom) FROM Tab1 WHERE Tab2.id1 = Tab1.id1) dummy
SET Tab2.nom = (temp: = temp+1)
WHERE Tab1.id1 = Tab2.id1;

3

Re: a variable in UPDATE

Still a variant - to esteem attentively FAQ: Numbering of lines and other questions about usage of variables .

4

Re: a variable in UPDATE

Friends as it is good that you such sympathetic. I already was, was accumulated by patience on day two. Ah is not present. All quickly and on business. Two times, fairly tried to build an example dataful. The editor did not allow to draw beautifully. I will try once again ...

5

Re: a variable in UPDATE

State before request performance
Tab1
Id nom
A | 1
A | 2
B | 1
A | 3
C | 1
C | 2
C | 3
B | 2
Tab2
Id nom
A | 0
A |0
A |0
C |0
C |0
B |0
A |0
I want that the request wrote down in Tab2 such nom
Tab2
Id nom
A | 4
A |5
A |6
C |4
C |5
B |3
A |7

6

Re: a variable in UPDATE

2 wrote:

Two times, fairly tried to build an example dataful. The editor did not allow to draw beautifully.

Tag Table (the button above, between SUB and Other). Given to divide into columns commas.

Title 1, Title 2
Value 1, Value 2

Title 1, Title 2
Value 1, Value 2

7

Re: a variable in UPDATE

Well then I truly specified - read FAQ, the second message, points 1 and 2. Normal numbering of lines in group. With that only a difference that starting value @rownum at group change undertakes from a subquery, instead of strictly zero.

8

Re: a variable in UPDATE

Thanks. I will read. But you far do not leave)

9

Re: a variable in UPDATE