Alle de raekker hvor Billede = NULL skal opdateres. Dataen der skal indsaettes i Billede skal tages fra en anden raekke der har samme kode - og samme klient hvis samme klient eksisterer. Ser man i eksemplet oppe over, saa er raekke 1) og 2) eksempler paa raekker der skal opdateres. Raekke 1) SKAL opdateres fra raekke 4), hvorimod raekke 2) enten skal opdateres fra raekke 3) eller 4) - det er ligegyldigt.
Jeg har proevet at lave en UPDATE FROM.... problemet er at den ikke tolorerer en ORDER BY clause... Skal jeg goere dette via 2 queries(een hvor der er klient specifik og een hvor det ikke er klient specifik), eller er der en smartere maade at goere dette paa?
2 queries lyder fornuftig i den første opdaterer fra der hvor både klient og kode er lige med i den anden bare distinct hvor kode er lige med. eller er jeg bare forvirret?
Desvaerre er eksemplet dette kun en simplificeret version af den virkelige verden hvilken kraever en del JOINS osv => det tager tid. Hvis jeg kunne noejes med 1 query, saa ville det vaere det bedste.
This is NOT tested UPDATE T1 SET T1.Billede = (SELECT TOP 1 Billede FROM T1 AS T2 WHERE T1.Klient = T2.Klient and t1.Kode = T2.Kode and (NOT T2.Billede Is Null)) WHERE T1.Billede) Is Null
Terry>> It wont work as expected, since you are not taking the ORDER BY into account. The way you handle it is to always take a specific client, which isn't my case.
If you have an ID (primary key) then you could try
UPDATE T1 SET T1.Billede = (SELECT TOP 1 Billede FROM T1 AS T2 WHERE t1.Kode = T2.Kode and (NOT T2.Billede Is Null) ORDER BY T2.ID) WHERE (T1.Billede) Is Null
otherwise you need to change ORDER BY T2.ID to a field in the table which you can sort on
I need to do something like this (have not tested if it is valid sql)
UPDATE T1 SET T1.Billede = (SELECT TOP 1 Billede FROM T1 AS T2 WHERE t1.Kode = T2.Kode and (NOT T2.Billede Is Null) ORDER BY CASE T2.Klient WHEN T1.Klient THEN 1 ELSE 0 END) WHERE (T1.Billede) Is Null
I made the table simple, so it is easier to understand.
I need to update rows where picturepath is null. The value should be taken from another row in the same table, WHERE the code is the same and the picturepath is not null.... AND PREFEREBLY from the same client (if not, then whatever client).
The one I sugested works. Thanks for helping out...
UPDATE T1 SET T1.Billede = (SELECT TOP 1 Billede FROM T1 AS T2 WHERE t1.Kode = T2.Kode and (NOT T2.Billede Is Null) ORDER BY CASE T2.Klient WHEN T1.Klient THEN 1 ELSE 0 END) WHERE (T1.Billede) Is Null
You should be able to update one or more columns in the same record jsut by seperating the fields with a ,
UPDATE T1 SET T1.Billede = (SELECT TOP 1 Billede FROM T1 AS T2 WHERE t1.Kode = T2.Kode and (NOT T2.Billede Is Null) ORDER BY CASE T2.Klient WHEN T1.Klient THEN 1 ELSE 0 END), T1.SomeOtherField = (....) WHERE (T1.Billede) Is Null
yes... the problem is that the 2 columns being updated needs to be updated with data from the same row found in:
(SELECT TOP 1 Billede FROM T1 AS T2 WHERE t1.Kode = T2.Kode and (NOT T2.Billede Is Null)
I guess it is deep overhead... to make the same query appear twice in the update statement, but maybe that is the only solution :-) It is not a big deal though.
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
