SQL Update mit Unterabfrage aus anderer Tabelle

[winscheil]

Hallo,

ich habe eine Tabelle FIRMA mit folgendem Aufbau:
FIRMA, LIEFERANTENSTATUS
100100, aktiv
100200, aktiv
100300, aktiv
...

und die Tabelle BESTELLUNG:
FIRMA, BESTELLDATUM
100100, 25.10.2016
100100, 12.12.2017
100200, 10.05.2016
100300, 02.04.2018

Ich möchte jetzt die Spalte LIEFERANTENSTATUS in der Tabelle FIRMA updaten.

Bei allen Firmen, deren letzte Bestellung < 01.01.2017 ist, soll die Spalte LIEFERANTENSTATUS abgeändert werden auf den Wert 'inaktiv'.

Ich habe das mit genau einer Firma hinbekommen:
update FIRMA

set LIEFERANTENSTATUS = 'inaktiv'

where

(select MAX(t1.BESTELLDATUM)

from BESTELLUNG t1 where t1.FIRMA = '100200'

group by FIRMA)

< '01.01.2017'

and firma = '100200'


Leider habe ich keine Ahnung, wie ich alle Datensätze der Tabelle FIRMA update.
Ich hätte es so probiert, aber da kommt eine Fehlermeldung:
Die Unterabfrage hat mehr als einen Wert zurückgegeben. Das ist nicht zulässig, wenn die Unterabfrage auf =, !=, <, <=, > oder >= folgt oder als Ausdruck verwendet wird.

update FIRMA

set LIEFERANTENSTATUS = 'inaktiv'

where

(select MAX(t1.DATUM)

from BESTELLUNG t1 where t1.FIRMA = FIRMA

group by t1.FIRMA)

< '01.01.2017'


Ich hoffe, ich habe alles verständlich erklärt.
Kann mir da jemand helfen?

 

[akretschmer]

test=*# select * from firma ;
 firma  | status
--------+--------
 100100 | activ
 100200 | activ
 100300 | activ
(3 Zeilen)

test=*# select * from bestellung ;
 firma  |  datum   
--------+------------
 100100 | 2016-10-25
 100100 | 2017-12-12
 100200 | 2016-05-10
 100300 | 2018-04-02
(4 Zeilen)

test=*# select firma,max(datum) from bestellung group by firma having max(datum) >= '2017-01-01' ;
 firma  |  max   
--------+------------
 100100 | 2017-12-12
 100300 | 2018-04-02
(2 Zeilen)
test=*# update firma set status = 'inactiv' where firma not in (select firma from bestellung group by firma having max(datum) >= '2017-01-01');
UPDATE 1
test=*# select * from firma ;
 firma  | status  
--------+---------
 100100 | activ
 100300 | activ
 100200 | inactiv
(3 Zeilen)

 

[winscheil]

Das ist eigentlich genau, das was ich suche.
Danke!
Aber ich habe noch ein Problem:
Zuerst nehme ich Ihren Select:
select FIRMA,max(DATUM)as DATUM from BESTELLUNG groupby FIRMA HAVINGMAX(DATUM)>='2017-01-01'



Wie man sehen kann, ist die Firma 100433 nicht dabei beim Ergebnis des Selects (siehe Bild).
Somit müsste bei dem Update diese Firma geupdated werden.

Wenn ich jetzt das Update ausführe:
update FIRMA
set ANP_LIEFERANTENSTATUS 'inaktiv'
where FIRMA NOTIN(select FIRMA from BESTELLUNG groupby FIRMA HAVINGMAX(DATUM)>='2017-01-01')

Bekomme ich die Meldung, dass KEIN Datensatz geändert wurde: 0 Zeilen betroffen



Der Update grundsätzlich funktioniert:
update FIRMA set ANP_LIEFERANTENSTATUS ='inaktiv' where FIRMA ='100433'



Was mache ich falsch bei dem Update-Befehl?

 

[winscheil]

Das NOT IN habe ich natürlich getrennt geschrieben.

 

[akretschmer]

was liefert denn das Subselect, und warum killst Du Leerzeichen?

 

[winscheil]

Du meinst mit dem Subselect folgenden Select, oder?
select FIRMA from BESTELLUNG group by FIRMA having max(DATUM) >= '2017-01-01'

Dieser select liefert 515 Zeilen.

Ich hab den Select aus dem MS SQL Management Studio kopiert und hab nicht gesehen, dass er beim Einfügen hier die Leerzeichen gekillt hat.
Natürlich sieht der Update Select so aus:
update FIRMA
set ANP_LIEFERANTENSTATUS ='inaktiv'
where FIRMA not in (select FIRMA from BESTELLUNG group by FIRMA having max(DATUM)>='2017-01-01')

 

[akretschmer]

ist da 100433 mit dabei?

 

[winscheil]

nein, ist nicht dabei.
und da wir ja als Bedingung bei dem Update das NOT IN haben, müsste ja dieser Datensatz geupdated werden, oder?

 

[akretschmer]

Exakt.

 

[winscheil]

hm, und woran könnte es liegen, dass es trotzdem nicht funktioniert?

 

[akretschmer]

der select liefert bei dir 515 Zeilen, das ist etwas mehr als 512 - vielleicht hat Deine DB ein Limit mit 2^9 Einträgen in solch einer Liste?

ist jetzt aber nur eine wilde Vermutung...

 

[winscheil]

das kann es nicht sein.
Die DB kann viel mehr...

 

[ukulele]

Gibt es Zeilen in denen Firma NULL ist? Diese solltest du Filtern, wenn dein Subselect NULL liefert kann das die Ursache sein. Dann würdest du quasi etwas wie = NULL oder <> NULL prüfen, was nicht geht.