[SQL] 2 Records "omwisselen", hoe doet de Tweaker het?

[Wijnbo]

Tabel:

hutsefluts - int

Data:

code:

1
2
3

Tabel:

hutseflutsid - int
sequence - int
name - varchar

Data:

code:

1 1 Jantje
1 2 Pietje
2 1 Pietje

PK over hutseflutsid & sequence.


Nu wil ik graag de volgorde van Pietje en Jantje omwisselen bij hutseflutsid = 1.

Dus:

code:

1 2 Jantje
1 1 Pietje
2 1 Pietje

Hoe doet de tweaker dit? Ik kom zelf niet verder dan alle records wegknikkeren waar hutseflitsid = 1 en vervolgens weer inserten. Draak van een oplossing natuurlijk. Zijn er betere oplossingen zonder de key aan te hoeven te passen?

Want een update statement gaat dus niet... want na de eerste update wordt de PK rule overtreden.

Mooiste zou zijn een "wissel de boel maar om en controleer dan pas of het klopt" oplossing, of gaat dit wel goed in een transactie?

[freak-ish]

Order by
1 asc,
2 asc

1 en 2 verwijst naar variable/kolom 1 en 2

[ Voor 10% gewijzigd door freak-ish op 10-03-2011 10:31 ]

[tss68nl]

Een SQL tabel garandeert geen volgorde. Dat je het grootste gedeelte van de tijd een tabel in een bepaalde volgorde terug krijgt, wil niet zeggen dat dit altijd gebeurt.

Je hoort zoals freak-ish aangeeft, bij het opvragen middels een 'order by' te zorgen dat je de gegevens in de correcte volgorde ophaalt.

[Wijnbo]

freak-ish schreef op donderdag 10 maart 2011 @ 10:27:
Order by
1 asc,
2 asc

1 en 2 verwijst naar variable/kolom 1 en 2

Eh, snap je wel wat ik bedoel ?

Ik wil eigelijk 2 "volgnummers" omwisselen.

Het eerste update statement gaat al kapot, omdat je een UPDATE tabel SET sequence = 2 doet wat de key ongeldig maakt.


Ik wil dus niet sorteren, ik wil data bewerken!


Ff iets duidelijkere uitleg misschien : Stel je 2 order regels voor in een order systeem, die beide een volgnummer hebben, en een order id.

Echter, nu wil ik de beide regels van plaats verwisselen, dus volgnummer 1 wordt 2 en volgnummer 2 wordt 1. Bij een PK over order en volgnummer, doet de PK krak, want na het eerste update statement zit twee maal volgnummer 2 en 0 maal volgnummer 1 in de tabel voor de zelfde order.

[ Voor 38% gewijzigd door Wijnbo op 10-03-2011 10:38 ]

[NMe]

Wijnbo schreef op donderdag 10 maart 2011 @ 10:34:
[...]

Eh, snap je wel wat ik bedoel ?

Ik wil eigelijk 2 "volgnummers" omwisselen.

Het eerste update statement gaat al kapot, omdat je een UPDATE tabel SET sequence = 2 doet wat de key ongeldig maakt.

Als je geen foreign key constraints hebt: een van de twee sequence 0 geven, de andere dezelfde sequence geven die dat record eerst had en dan dat 0-record zijn nieuwe waarde geven. Standaardoplossing voor wisseltruukjes.

[Wijnbo]

NMe schreef op donderdag 10 maart 2011 @ 10:41:
[...]

Als je geen foreign key constraints hebt: een van de twee sequence 0 geven, de andere dezelfde sequence geven die dat record eerst had en dan dat 0-record zijn nieuwe waarde geven. Standaardoplossing voor wisseltruukjes.

Zoiets had ik ook al bedacht, het 1e om te wisselen record een MAX(sequence) geven en dan de tweede naar de eerste, en dan de MAX naar de oude waarde van de tweede.

Maar dit voelt heel erg

[Janoz]

A en B verwisselen kan als volgt:

(A is kleinste)

update tabel set kolom=kolom+1 where kolom >= A
update tabel set kolom=A where kolom = B+1
update tabel set kolom=kolom-1 where kolom >= B

Wel alles in één transactie doen.


Ik zou trouwens sowieso geen PK van die twee velden maken. Als het blijkbaar aanpasbare velden zijn dan zijn ze erg slecht geschikt voor PK. hutsefluts lijkt me typisch een foreign key naar iets anders. Voeg gewoon een nieuwe ID toe en zet op de twee kolommen een unique constraint.

[ Voor 38% gewijzigd door Janoz op 10-03-2011 10:47 ]

[Niemand_Anders]

Waarom gebruik je voor de sequence geen grotere ranges? Verwacht jij miljoenen sequence ids of zo? Anders kun je natuurlijk ook een veelvoud van 10 als range aanhouden en dan kun je 1. gemakkelijk de volgorde aanpassen en 2. kan er later eenvoudig ook een record tussen twee bestaande worden geplaatst.

Ik gebruik een soortgelijk systeem voor database trees en elke nacht draait er een batch welke controleert of er sequenceids zijn welke geen veelvoud van 10 zijn, als deze worden gevonden wordt de 'relist' procedure aangeroepen welke alle sequences correct zet. Werkt al ruim 10 jaar zonder problemen.

Degene welke vroeger op de C64, Atari ST of MSX basic hebben geschreven leerde al snel om de statement id ook met een veelvoud van 10 te laten oplopen. Op de commodore met de KCS Power cardridge kon je dan een 'relist 100 10' commando geven welke je code opnieuw nummerde vanaf 100 met een interval van 10. Mijn database relist procedure werkt op een soort gelijke manier.

Omwisselen is dan erg simpel:
int c = sequence van B'
B krijgt sequence A + 1
A krijgt sequence opgeslagen in 'c'

B krijgt dus sequence 11, terwijl A sequence 20 krijgt.

[RobIII]

Ik snap er geen hol van...

SQL:

select foo, bar, foobar
from mytable
order by foo asc, bar desc

Als het enkel om de 1 en 2 omdraaien gaat (dus je wil 2, 1, 3, 4, 5, ...) dan doe je:

SQL:

select foo, bar, foobar
from mytable
order by foo asc, case when bar = 2 then 0 else bar end

En anders: Zie Janoz

[ Voor 3% gewijzigd door RobIII op 10-03-2011 10:53 ]

[Wijnbo]

RobIII schreef op donderdag 10 maart 2011 @ 10:52:
Ik snap er geen hol van...

SQL:

select foo, bar, foobar
from mytable
order by foo asc, bar desc

Als het enkel om de 1 en 2 omdraaien gaat (dus je wil 2, 1, 3, 4, 5, ...) dan doe je:

SQL:

select foo, bar, foobar
from mytable
order by foo asc, case when bar = 2 then 0 else bar end

En anders: Zie Janoz

Het gaat om de data omwisselen, niet volgorde van uitpoepen.

Gewoon 2 getalletjes omwisselen in 2 records, alleen zijn die getalletjes onderdeel van de PK, waardoor je deze dus breekt na de eerste update. In een transactie lijkt het wel te werken inderdaad, hm, moet ik alleen wel veel ombouwen...

Dus, nog even voor de duidelijkheid:

code:

[      PK       ]  
[      1[      1][ Keesje]
[      1[      2][ Jantje]

Gewenste situatie

code:

[      PK       ]  
[      1[      2][ Keesje]
[      1[      1][ Jantje]

Dus...

UPDATE tabel SET sequence = 1 WHERE name = 'Jantje'
UPDATE tabel SET sequence = 2 WHERE name = 'Keesje'

Gevolg na 1e update statement :

code:

[      PK       ]  
[      1[      1][ Keesje]
[      1[      1][ Jantje]

KRAK
En Niemand_Anders : Nofi, maar dit voelt heel erg als een 1989 oplossing.

[ Voor 27% gewijzigd door Wijnbo op 10-03-2011 11:01 ]

[Janoz]

Dat een oplossing oud is maakt hem niet automatisch slechter. Wanneer het omwisselen van items iets is wat heel vaak gaat gebeuren dan is dat zelfs een te prefereren oplossing.

[tss68nl]

Wijnbo schreef op donderdag 10 maart 2011 @ 10:56:
[...]
En Niemand_Anders : Nofi, maar dit voelt heel erg als een 1989 oplossing.

De reden waarom dit aanvoelt als een 1989 oplossing, is omdat dit de enige oplossing is binnen het gekozen database ontwerp. Daarmee kan je dus stellen dat het ontwerp aanvoelt als 1989...en dus ook alle mogelijke oplossingen voor dit specifieke probleem.

Wellicht kan je beter het onderliggende probleem tackelen:

• Waarom zit een kolom die kan wijzigen in de PK? Veel databases hebben een clustered index voor een PK, waardoor regels fysiek moeten verschuiven in de bestanden bij een update.
• Waarom zit de data er in de eerste plaats al verkeerd om in?
• Als er een goede reden is, waarom data niet meteen correct geschreven kan worden, dan is het wellicht te overwegen om de uiteindelijke volgorde in een andere kolom vast te leggen dan de originele. Ik zou mijn originele vastlegging niet kwijt willen raken. Mocht de logica ooit niet helemaal perfect blijken, dan heb je tenminste nog een originele basis waar vanuit je kan herberekenen.

[MSalters]

Even brainstormen, wat is er mis met

UPDATE tabel SET sequence = case sequence when 1 then 2 when 2 then 1 end WHERE sequence = 1 or sequence = 2

Vziw moet de PK integriteit voor en na een update gewaarborgd zijn, maar is het de verantwoordelijkheid van de engine om te zorgen dat er geen al te gekke dingen tijdens de update gebeuren.

[Onno]

In databases waarin je de evalatie van constraints kunt uitstellen tot het einde van de transactie (of in ieder geval tot na het huidige statement) kun je gewoon zoiets doen:

SQL:

update tabel set kolom = (A + B) - kolom where kolom in (A, B)

In PostgreSQL kan dit bijvoorbeeld door de bewuste constraint deferred te maken.

[Armageddon_2k]

XOR swappen
Extra variabelen zijn voor mietjes

X := X XOR Y
Y := X XOR Y
X := X XOR Y

[Onno]

Extra statements ook.

Daarnaast is dit niet echt van toepassing op het gegeven probleem: niet alleen voorkom je er geen constraint violations mee (wat als X ^ Y al bestaat?), maar je hebt er ook gewoon niets aan in SQL: wat gaat je where-clause zijn voor de drie statements die je nodig hebt?

Je kunt beginnen met

SQL:

update tabel set kolom = kolom # Y where kolom = X

maar wat doe je daarna?

[Armageddon_2k]

Onno schreef op donderdag 10 maart 2011 @ 12:53:
Extra statements ook.

Daarnaast is dit niet echt van toepassing op het gegeven probleem: niet alleen voorkom je er geen constraint violations mee (wat als X ^ Y al bestaat?), maar je hebt er ook gewoon niets aan in SQL: wat gaat je where-clause zijn voor de drie statements die je nodig hebt?

Je kunt beginnen met

SQL:

update tabel set kolom = kolom # Y where kolom = X

maar wat doe je daarna?

Het zijn uiteraard 3 opvolgende query''s.
je kan ook:

X=X+Y
Y=X-Y
X=X-Y

Gebruiken.
Maar je zit idd wel met het probleem als X+Y al bestaat. Hangt ook een beetje van de toepassing af.
Denk dat als je wilt gaan swappen je sowiso een waarde nodig gaat hebben waarvan je weet dat deze nog niet in de tabel bestaat.

[ Voor 15% gewijzigd door Armageddon_2k op 10-03-2011 13:13 ]

[Remus]

Als dit alleen voor presentatie is, dan zou ik ervoor kiezen om:
1) de table een aparte numerieke PK te geven
2) een FK naar hutsefluts
3) een sequence die buiten enige constraint valt, en die je dus naar hartelust kan aanpassen.

[Onno]

Armageddon_2k schreef op donderdag 10 maart 2011 @ 13:11:
[...]


Het zijn uiteraard 3 opvolgende query''s.

Uiteraard, maar geef nou eens een voorbeeld van hoe je dat in SQL zou willen doen? Je voorbeeldje werkt alweer met variabelen, als je dat vertaalt naar SQL zie je al snel dat het helemaal niet zo'n handige oplossing is. (en zo bracht je het wel)

Denk dat als je wilt gaan swappen je sowiso een waarde nodig gaat hebben waarvan je weet dat deze nog niet in de tabel bestaat.

Nee, zie hierboven de oplossingen van MSalters en mijzelf.

[tss68nl]

Stop gewoon met het zoeken naar de beste oplossing. Er is geen goede oplossing, omdat het db ontwerp niet ondersteunt wat je wilt doen.

Introduceer een nieuwe kolom waar je de berekende nieuwe volgorde in kwijt kunt, done.

[freak-ish]

NMe schreef op donderdag 10 maart 2011 @ 10:41:
[...]

Als je geen foreign key constraints hebt: een van de twee sequence 0 geven, de andere dezelfde sequence geven die dat record eerst had en dan dat 0-record zijn nieuwe waarde geven. Standaardoplossing voor wisseltruukjes.

Nope, had je inderdaad anders begrepen

[ReenL]

Kan het niet beter verwoorden als dit:

Janoz schreef op donderdag 10 maart 2011 @ 10:44:
A en B verwisselen kan als volgt:

(A is kleinste)

update tabel set kolom=kolom+1 where kolom >= A
update tabel set kolom=A where kolom = B+1
update tabel set kolom=kolom-1 where kolom >= B

Wel alles in één transactie doen.


Ik zou trouwens sowieso geen PK van die twee velden maken. Als het blijkbaar aanpasbare velden zijn dan zijn ze erg slecht geschikt voor PK. hutsefluts lijkt me typisch een foreign key naar iets anders. Voeg gewoon een nieuwe ID toe en zet op de twee kolommen een unique constraint.

Gatenkaas maken van je sorteer kolom etc zijn alleen symptoom bestrijding en raad ik af. Even opletten bij delete dat je alles wat er na komt ook update met kolom = kolom - 1.

Zorg ook dat er een aparte index op alleen sequence staat. (Ik weet niet hoe groot je tabel gaat worden).