On 6/15/06, theo <[EMAIL PROTECTED]> wrote:
Now, there is a new employee "John" with "a job better than Peter's"
His "importance" is one more than peter's, and all other move up +1;
So the list should look like this after inserting "John":
Peter; 24years, some job, 0
John; 28years, somewhat better job, 1
Anna; 22years, better job, 2
Frank; 40 years, chief, 3
Which is the most effiicient way (SQL Statements) to "insert" or
"delete" John in this example?
Barrys solution works fine, but you might want a little more details
(no offence Bar!) :-)
Before inserting a new employee:
UPDATE workers SET importance = importance + 1 WHERE importance > X
Where "X" is the position that the new employee should have. This
makes every person "under" the new employee raise the importance value
by one.
Just reverse the method before removing an employee:
UPDATE workers SET importance = importance - 1 WHERE importance > X &&
importance > 0
Which would "move" all the employees which were under the given
employee (with importance X) up a step, as long as they're not on top
(importance 0).
...just realize this might get you more confused than clearing it out
for you, but what the hey.
Best regards
--
Kim Christensen
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
