SQL hilfestellung

[Frank_B]

Moin ich habe eine sehr einfache Datenbank (test) mit 630.000 Datensätzen
content | count
00050474006 | 0,1 oder 2
(content) ist eine fortlaufende 11Stellige Zahl beginnend mit 00050474006

jetzt möchte ich jeden 9000'ten Datensatz
der mit -> like '000_____005' und mit 000_____006 endet
das jeweilige Feld (count) mit 2 beschreiben.

und ich habe keinen Plan wie man nur die 140 Datensätze auswählt / ändert

gruß Frank

 

[dabadepdu]

jetzt möchte ich..

Soll es exakt jeder 900ste sein oder ist das einfach eine Statistiksache, bei der irgendein Random Record verwendet werden kann/soll, eben 140 zufällig in Summe? Erst das Pattern selektieren, dann davon jeden 9000. DS?
Die Frage ist: Welche Datensätze sollen genau geändert werden?

Erstmal solltest Du ein Select Statement bauen, das die Menge der Datensätze zurück gibt, die geändert werden sollen.
Alternativ ein Statement, das alle Datensätze zurück gibt und eine zusätzliche Spalte enthält, die zeigt, ob es ein Update Fall ist oder nicht.

Das ist noch kein Update, aber es gibt Dir eine Idee, was passieren muss.

Falls das mit dem 9000. wirklich eine Rolle spielt, kannst Du mal sowas machen:

select 
  ROW_NUMBER () 
  OVER (
    ORDER BY 1
  ) RowNum,
  x.* from test x
-- where strng like '<pattern1>' and strng like '<pattern2>'
[code]
wahlweise mit oder ohne Where Clause

Außerdem interessant: 
Ist die Spalte content eindeutiger Schlüssel? Könnte bei einer fortlaufenden Zahl sein.
(Oder ist sie bloß erstmal das Ergebnis irgendeines anderen Statements?)

Hat sie einen Konstraint auf die Eindeutigkeit?

 

[Frank_B]

Wow, bin eher ein ziemlicher Anfänger...
Es gibt nur diese beiden Zeilen kein index.
Von der Selektion her muss quasi der
Erste Datensatz dann der 9000'ste
Dann der "nächste erste(9001)
Dann der 18000, 18001
Dann 27000, 27001
Selektiert werden...
Aber ein fortlaufender Index ist leider nicht vorhanden.
Die Daten werden aus einem Verarbeitungsprozess von einem Laserscanner in diese Datenbank geschrieben.
Ab und zu geht etwas schief und die Daten
Im Feld (count) müssen nachgearbeitet werden mit dem Wert '2'
Datensatz 1
Datensatz 9000
Datensatz 9001
Datensatz 18000
18001
27000
27001
....halt fortlaufend aus Feld (content)

Gruß Frank

 

[ukulele]

Was verstehst du unter einem fortlaufenden Index? Ich hätte angenommen, das content als fortlaufende Zahl die eindeutige Reihenfolge vorgibt. Und eine Reihenfolge wird benötigt - idealerweise eine Sortierung die eindeutig ist und in der nie zwei identische Werte vorkommen.

Für sowas bietet sich ROW_NUMBER() an. Ich bin allerdings verwundert, ROW_NUMBER() war meine ich in SQLite nicht verfügbar. Das scheint sich eventuell geändert zu haben, dann wäre die Version sicherlich relevant.

SQLite ROW_NUMBER() Function

This tutorial shows you how to use the SQLite ROW_NUMBER() to assign a sequential integer to each row in the result set of a query.

www.sqlitetutorial.net

Es gibt zwar Möglichkeiten das auch ohne umzusetzen, das dürfte aber alles andere als performant laufen:

How to use ROW_NUMBER in sqlite

Here is my query given below. select * from data where value = "yes"; My id is auto increment and below there is result of given query. id || value 1 || yes 3 || yes 4 || yes 6 || ye...

stackoverflow.com

Teste also, ob ROW_NUMBER() akzeptiert wird.

Abhängig davon, welchen Typ content hat, musst du in der WHERE-Bedingung eventuell erst konvertieren. Da du mit LIKE arbeiten willst, wäre VARCHAR() ein guter Datentyp. Ich mache nichts mit SQLite, die Syntax müsste man noch raus suchen.

SELECT *
FROM (

SELECT *,
ROW_NUMBER() OVER (ORDER BY content) AS zeile
FROM tabelle
WHERE content like '000_____005'
AND content LIKE '000_____006'

) t
WHERE t.zeile BETWEEN t.zeile / 9000 * 9000 AND t.zeile / 9000 * 9000 + 1

Mit dem BETWEEN musst du auch mal testen, es ist schon spät vielleicht ist das Quatsch ;-) Durch das teilen und multiplizieren einer ganzen Zahl nutze ich normalerweise die dann falsche Rundung, um glatte 9000er Schritte zu erkennen.

 

[Frank_B]

-Was verstehst du unter einem fortlaufenden Index? -->Ein Datenbankfeld mit fortlaufender eindeutiger Nummerierung (Access Begriff)
-das content als fortlaufende Zahl die eindeutige Reihenfolge vorgibt, idealerweise eine Sortierung die eindeutig ist und in der nie zwei identische Werte vorkommen.--> So! ist es

Moin, ich habe hier nur den (DB-Browser für SQLite Version 3.13.1) in meinem Windows 10.
(Fast wie Access/Excel, Tabellendaten anschauen und direkt editieren, SQL Kommandos nutzen)

Dieser versteht leider das ROW_NUMBER() nicht.
so etwas schon:
update (meineDatenbank)
set count = 1
where content between '00050969006' and '00050978005'

 

[ukulele]

Scheint in 2019 ein Problem von DB-Browser gewesen zu sein:

Support for SQL Windowing · Issue #1893 · sqlitebrowser/sqlitebrowser

Details for the issue It seems that sqlitebrowser does not support SQL Windowing. The following SQL statement: "select symbol, exchange, row_number() over(partition by symbol) from downloads;" gene...

github.com

Ein bisschen sonderbar, da ist von höheren Versionsnummern in der Nightlybuild die Rede, vielleicht einen Versuch wert. Ohne ROW_NUMBER() wird es unter Umständen ekelig.

Dein Beispielquery kann auch funktionieren wenn, content lückenlos ist und keine Dubletten vorkommen können. Dann kann man auch auf die Window-Funktion ROW_NUMBER() verzichten. Teste doch mal das Ergebnis von

SELECT * FROM tabelle
WHERE content BETWEEN content / 9000 * 9000 AND content / 9000 * 9000 + 1

 

[Frank_B]

-wenn, content lückenlos ist und keine Dubletten vorkommen können--> das ist der Fall!

Ausführung wurde ohne Fehler beendet.
Ergebnis: 0 Zeilen in 1666 ms zurückgegeben
In Zeile 1:
SELECT * FROM labels
WHERE content BETWEEN content / 9000 * 9000 AND content / 9000 * 9000 + 1

 

[Frank_B]

SELECT * FROM labels
WHERE content like '000_____005'
..gibt 630 Zeilen zurück
und
WHERE content like '000_____006'
..gibt auch 630 Zeilen zurück
da meine Datenbank ja 630.000 Zeilen hat (content von 00050474006 bis 00051104005)

Und bei der korrekten/gewünschten Selektion sollte es 70x -> like '000_____005' und auch 70x -> like '000_____006'
also 140 Datensätze ausgeben (Weil: 630.000 / 9000 = 70 ist) , dann wäre es korrekt

 

[ukulele]

Welches Format hat denn die Spalte, VARCHAR()?

 

[Frank_B]

 

[dabadepdu]

Moin, ich habe hier nur den (DB-Browser für SQLite Version 3.13.1) in meinem Windows 10

Ja, es gibt deutlich neuere Versionen, die verstehen Rownumber().
Es gibt eine Doku auch zu sqlite.
Wenn Du dir eine aktuelle DLL für sqlite runterlädst oder sonst irgendeine Umgebung, die mit aktuellen Versionen arbeitet, dann bist Du mit rownumber gut bedient.
Wenn das alles von anderen Prozessen abhängt, wo so eine aktuelle Version nicht zur Verfügung steht, dann ist es vielleicht problematisch.
Ich finde, es schadet in der IT Welt nicht, auch an solchen Stellen auf Aktualität zu achten.
Windows 10 ist z.B. nicht mehr so aktuell. Am besten umsteigen auf Linux

Ab und zu geht etwas schief und die Daten
Im Feld (count) müssen nachgearbeitet werden mit dem Wert '2'
Datensatz 1
Datensatz 9000
Datensatz 9001

Also es geht nur um die Ordnungszahl?! Nicht um ein where ..like ?
Ok, scheint eine Alternativlösung zu sein. Wenn das Like Pattern die richtigen Datensätze ausspuckt, ist doch der erste Schritt geschafft.
Dann muss das Select Statement zu einem Update Statement werden.

 

[Frank_B]

OK, hatte wohl zum Test der ROW_NUMBER etwas falsch gemacht...

dieser Test spuckt mi wenigsten die eine Hälfte meiner gewünschten daten aus:

WITH x as
(
SELECT ROW_NUMBER() OVER () as row_num,
content,
count
from labels
)
select * from x where row_num%9000=0

Datensatz9000
Datensatz18000
Datensatz27000
...
...

 

[Frank_B]

jetzt weis ich nur nicht wie das Feld (count) aus dem obrigem Ergebnis
mit bspw. count =2
setzen kann.

 

[Frank_B]

So, das hier gibt alles so aus wie ich es mir wünsche:
SELECT *
FROM (

SELECT *,
ROW_NUMBER() OVER (ORDER BY content) AS zeile
FROM labels
WHERE content like '000________'
)t
WHERE t.zeile BETWEEN t.zeile AND t.zeile / 9000 * 9000 + 1






Jetzt benötige ich nur noch eine kleine Hilfe
um im Ergebnis das Feld (count) zu ändern...

 

[ukulele]

Ja, es gibt deutlich neuere Versionen, die verstehen Rownumber().

Ich kenne DB-Browser für SQLite nicht aber die vorhandene Version scheint die latest stable zu sein. Daher war ich auch so irritiert, das ROW_NUMBER() schon vor 6 Jahren! eingebaut wurde.

Support for SQL Windowing · Issue #1893 · sqlitebrowser/sqlitebrowser

Details for the issue It seems that sqlitebrowser does not support SQL Windowing. The following SQL statement: "select symbol, exchange, row_number() over(partition by symbol) from downloads;" gene...

github.com

Liefert DB-Browser eventuell noch eine veraltete SQLite Version aus? Das kann man bestimmt aktualisieren.

Also es geht nur um die Ordnungszahl?! Nicht um ein where ..like ?
Ok, scheint eine Alternativlösung zu sein. Wenn das Like Pattern die richtigen Datensätze ausspuckt, ist doch der erste Schritt geschafft.
Dann muss das Select Statement zu einem Update Statement werden.

Jein. Der LIKE-Pattern liefert nur den ersten Datensatz aber bei 9000er Schritten muss man rechnen - oder sehr viele LIKE Varianten auflisten.

Besser wäre, eine Konvertierung zu Integer. Wenn ich das richtig sehe, ist das cast() und die Syntax ist gleich. In MSSQL kann ich nicht direkt von TEXT in INT casten, ich muss erst von TEXT zu VARCHAR() zu INT aber es geht grundsätzlich. Hier mal ein Beispiel von mir das bei dir laufen könnte:

SELECT    *
FROM    labels
WHERE    cast(cast(content AS VARCHAR(11)) AS INT) BETWEEN cast(cast(content AS VARCHAR(11)) AS INT) / 9000 * 9000 AND cast(cast(content AS VARCHAR(11)) AS INT) / 9000 * 9000 + 1

Als Update würde ich das erst formulieren, wenn der Select genau die richtigen Datensätze liefert

Proof of concept in MSSQL:

WITH t1(base) AS (
    SELECT    1
    UNION ALL
    SELECT    base + 10000
    FROM    t1
    WHERE    base < 620000
    ), t2(num,base) AS (
    SELECT    base,base
    FROM    t1
    UNION ALL
    SELECT    num + 1,base
    FROM    t2
    WHERE    num + 1 < base + 10000
    ), labels(content,num) AS (
    SELECT    cast(right('0000000000' + cast(num + 50474006 AS VARCHAR(11)),11) AS TEXT),
            num
    FROM    t2
    )
SELECT    *
FROM    labels
WHERE    cast(cast(content AS VARCHAR(11)) AS INT) BETWEEN cast(cast(content AS VARCHAR(11)) AS INT) / 9000 * 9000 AND cast(cast(content AS VARCHAR(11)) AS INT) / 9000 * 9000 + 1
ORDER BY num
OPTION(MAXRECURSION 10000)

PS:
Da war ich etwas zu langsam. Wenn mein Statement funktioniert, wäre es

UPDATE labels
SET count = 2
WHERE    cast(cast(content AS VARCHAR(11)) AS INT) BETWEEN cast(cast(content AS VARCHAR(11)) AS INT) / 9000 * 9000 AND cast(cast(content AS VARCHAR(11)) AS INT) / 9000 * 9000 + 1

Das mit ROW_NUMBER() kann man sicher auch umstellen. Wäre mir jetzt bei der Syntax für SQLite aber nicht sicher.