[Gelöst!]SQL/SQLITE: Feldinhalt einer Tabelle um Feldinhalt anderer Tabelle bereingen

[Captn. Jinguji]

Es gibt eine Anwendung, die User-Kommentare ("Usernamen" heißen bei YT ja "Channels") unter YouTube-Vids in eine Sqlite-DB herunter lädt. Klappt prima, die DB ist auch vernünftig designed (-> Normalform).

Ist ein YT-Kommentar ( Bsp: eines Channels "@yy" -YT dekoriert die meisten Channelnamen mit einem vorangestellten '@'- mit Rohtext: "Ja, genau" ) eine Antwort auf einen vorherigen Kommentar eines Channels "@xx", injiziert YT ein "@@xx " an den Anfang des Kommentartextes von yy, der dadurch nun de facto: "@@xx Ja, genau" lautet.

Da meine DB nun so langsam der Anzahl von Millionen Kommentaren entgegenrobbt, liegt der Gedanke ja nicht so fern, diese Injektion zu entsorgen, da YT-Channelnamen bis zu 30 Zeichen lang sind, die DB also zwischen 15 bis 25 MB nur für diese redundante "Information" entlastet werden kann; (ich habe noch andere signifikante Entlastungen in petto).

In PB Code ist das an sich ja kein Problem :
* Channel-Tabelle in eine List() einlesen,
* mit ForEach die List() abklappern
* per SQL mit "regexp" die einschlägigen Rows identifizieren
* und per SQL-REPLACE() oder PB-Replacestring()/Removestring() die Korrektur
vornehmen.

Aber an sich wäre es eleganter, das mit einem SQL-Statement zu ermachen, denn ich will das Verfahren auch für zahlreiche weitere Ballaste am Anfang der Kommentare einsetzen; dazu jedesmal eine Liste aufzubauen wäre blöd ( Man sieht erst per Sortierung hunderttausender Kommentare für sich, in welchem Umfang Leute Phräschen/Füllwörter oder einfach repetitiven Kappes in die Kommentare abwerfen).

Ich war schon soweit, per SQL SELECT eine Outputabelle produzieren zu können, die in zwei Spalten nebeneinander den identifizierten ChannelName-String und den um ein '@' längeren Substring vom Comment-Text-Anfang mit korrekter Länge rausschreibt ... "ja, dann ist es doch ein Klacks, den Update/Replace da drüberzulegen, nicht wahr? "
Ja, eben, NICHT wahr, ich krieg da die Kuve nicht.
Entweder bleibt der lästige String, oder cmnt_text ist nach dem Duchlauf NULL.

Es geht also darum, ein SQL-Script zu schreiben,
das als PseudoCode lautet:
"Ersetze in Tabelle CMNTS in Spalte CMNT_TXT in jeder Row,
in der am Anfang von Spalte CMNT_TXT ein '@'||CHANS.CHN_NAM steht,
ebendiesen Anfangsstring durch einen Leerstring;
(vulgo: lösche den String vo Anfang des CMNT_TXT).

CHANS.CHN_NAM kann beliebige Längen bis 31 Zeichen haben,
die meisten CH_NAM beginnen mit einem '@'.

Code: Alles auswählen

"Channel-" Tabelle CHANS
[CH_ID]		[CH_NAM]
0			@aaa
1			@bbbb
2			@ccccc
3			@dddddd


Tabelle CMNTS
[CMNT_ID]	[CMNT_TXT]				[CH_ID]
1			@@bbbb Sum Ting Wong	0
2			@@ccccc Wee Tu Lo		1
3			@@dddddd Hoe Lee Fuk	2
4			@@aaa Bang Ding Oww	3
5			@@aaab 💩			2


select t2.CHN_NAM AS AUTHOR, t1.CMNT_TXT as RECIPIENT_PLUS_MESSAGE
from CMNTS t1, CHANS t2 on t1.CHN_ID = t2.CHN_ID;

[AUTHOR]    [RECIPIENT_PLUS_MESSAGE]
@aaa		@@bbbb Sum Ting Wong
@bbbb		@@ccccc Wee Tu Lo
@ccccc		@@dddddd Hoe Lee Fuk
@dddddd		@@aaa Bang Ding Oww
@ccccc		@@bbbb 💩
Man sieht also, dass der @aaa dem @bbbb 
eine Antwort mit Text "Sum Ting Wong" sendet 
(die Ur-Nachricht von @bbbb ist hier ja egal)


Ziel  soll sein:
[AUTHOR]    [MESSAGE]
@aaa		Sum Ting Wong
@bbbb		Wee Tu Lo
@ccccc		Hoe Lee Fuk
@dddddd		Bang Ding Oww
@ccccc		💩

Alle folgenden SQL-Scripts laufen so im "DB Browser for SQLite"

Der SQL-Code zum Erstellen der Beispieltabellen

Code: Alles auswählen

CREATE TABLE if not exists "chans" (
	"chn_id"	         TEXT NOT NULL UNIQUE,
	"chn_nam"	TEXT NOT NULL,
	PRIMARY KEY("chn_id")
);
CREATE TABLE  if not exists "cmnts" (
	"CMNT_ID"	INTEGER NOT NULL UNIQUE,
	"CMNT_TXT"	TEXT NOT NULL,
	"CHN_ID"	        TEXT NOT NULL,
	PRIMARY KEY("CMNT_ID"),
	CONSTRAINT "MyChanId" FOREIGN KEY("CHN_ID") REFERENCES "chans"
);

delete from  cmnts;
delete from  chans;

-- drop   table cmnts;
-- drop   table chans;

Hier der SQL-Code zum Befüllen der Beispieltabellen

Code: Alles auswählen

insert into chans values('0', '@aaa');
insert into chans values('1', '@bbbb');
insert into chans values('2', '@ccccc');
insert into chans values('3', '@dddddd');

insert into cmnts ( CMNT_TXT, chn_id ) values (  '@@bbbb Sum Ting Wong','0');
insert into cmnts ( CMNT_TXT, chn_id ) values (  '@@ccccc Wee Tu Lo',1);
insert into cmnts ( CMNT_TXT, chn_id ) values (  '@@dddddd Hoe Lee Fuk',2);
insert into cmnts ( CMNT_TXT, chn_id ) values (  '@@aaa Bang Ding Oww',3);
insert into cmnts ( CMNT_TXT, chn_id ) values (  '@@bbbb 💩','2');

Hier der SQL-Code zum Abfragen der Beispieltabellen

Code: Alles auswählen

select * from chans;
select * from cmnts;
select t2.CHN_NAM AS AUTHOR, t1.CMNT_TXT as RECIPIENT_PLUS_MESSAGE
from CMNTS t1, CHANS t2 on t1.CHN_ID = t2.CHN_ID;

Sachdienliche Hinweise werden dankend entgegengenommen

[Andesdaf]

Code: Alles auswählen

UPDATE cmnts
SET cmnt_txt = replace(cmnt_txt, '@' || chn_nam, '')
FROM chans
WHERE substr(cmnt_txt, 2) LIKE chn_nam || '%'

[Captn. Jinguji]

Vielen Dank, Andesdaf!

"UPDATE FROM" war mir überhaupt nicht geläufig,
allerdings sagt die Doku -wie ich jetzt weiß- ja auch:

Code: Alles auswählen

2.2. UPDATE FROM

The UPDATE-FROM idea is an extension to SQL ... . 

Ich hatte die [sonstige] Doku AUSSER [grrrrrr!] für UPDATE rauf und runter gelesen und ganz wilde Konstrukte ausprobieren müssen. Zusammengerechnet DREI volle Tage im Teich dafür,
gut, dass das "nur für mich" und nicht in einem Kundenprojekt war.

Naja, kleiner Trost für mich:
immerhin zeigt die Existenz dieser Extension, dass es mit Standard-Mitteln wohl nicht so einfach wäre, wenn überhaupt möglich.