Re: [lms] Zapytanie mysql, update nodes i netdevices

19 Lut 2016


      UPDATE nodes n SET
location_address = (SELECT
                    CASE
                    WHEN post_address != '' THEN post_address
                    ELSE address
                    END as address
                    FROM customers c WHERE c.id = n.ownerid )
WHERE location_address IS NULL OR location_address = '';
UPDATE nodes n SET
location_city = (SELECT
                  CASE
                  WHEN post_city != '' THEN post_city
                  ELSE city
                  END as city
                 FROM customers c WHERE c.id = n.ownerid)
WHERE location_city IS NULL OR location_city = '';
UPDATE nodes n SET
location_zip = (SELECT
                CASE
                WHEN post_zip != '' THEN post_zip
                ELSE zip
                END as zip
                FROM customers c WHERE c.id = n.ownerid)
WHERE location_zip IS NULL OR location_zip = '';
UPDATE netdevices nd SET location = (
    SELECT CONCAT(location_city, ', ',location_address) AS location
    FROM nodes n
    WHERE n.netdev = nd.id LIMIT 1
)
WHERE location IS NULL OR location = ''
W dniu 19 lutego 2016 21:57 użytkownik Marcin marcin@nicram.net napisał:
...
dzięki
ostatecznie rozbiłem to na kilka zapytań
W dniu 19 lutego 2016 15:56 użytkownik Waldemar Dymkiewicz <
waldemar.dymkiewicz@easy-com.pl> napisał:
...
W dniu 2016-02-18 o 19:58, Marcin pisze:
skeiłem takie zapytanie
UPDATE nodes n SET
location_address = (SELECT
                    CASE
                    WHEN post_address != '' THEN post_address
                    ELSE address
                    END as address
                    FROM customers c WHERE c.id = n.ownerid ),
location_city = (SELECT
                  CASE
                  WHEN post_city != '' THEN post_city
                  ELSE city
                  END as city
                 FROM customers c WHERE c.id = n.ownerid),
location_zip = (SELECT
                CASE
                WHEN post_zip != '' THEN post_zip
                ELSE zip
                END as zip
                FROM customers c WHERE c.id = n.ownerid)
załatwia to adresy tylko komputerów gdyż one mają ownerid, co z
urządzeniami które mają ownerid=0 a są u klienta? komp jest połączony z tym
urządzeniem
Jest jeden mały problem...
w polach location_address location_city powinien sie znalesc ID z tabeli
location_
chyba ze w DIRze bylo inaczej bo juz nie pamietam.
W ogole tables nodes miala kolumne location_zip?
Ale jesli jest OK to:
UPDATE netdevices n SET
location_address = (
SELECT
location_address
FROM
nodes nn
WHERE n.id = nn.netdev
)
.
.
.
pisane na kolanie w mailu ale cos w ten desen

lms mailing list
lms@lists.lms.org.pl
http://lists.lms.org.pl/mailman/listinfo/lms
--
Pozdrawiam
Marcin / nicraM
-- 
Pozdrawiam
Marcin / nicraM