aktualizacjia adresów w urzadzeniach sieciowych
Witam chciałem przypisać do urządzeń sieciowych, ktore są u klientów, adresy lokalizacji z adresu klienta. do klienta należy komputer a komputer ten jest połączony z urządzeniem sieciowym. Właśnie temu urządzeniu chcę ustawić adres lokalizacji. skleciłem takie zapytanie: UPDATE netdevices nd SET location = (SELECT c.address from customers c, nodes n WHERE nd.id = n.netdev AND c.id = n.ownerid and c.address IS NOT NULL LIMIT 1) ale postgres krzyczy o błędzie: BŁĄD: pusta wartość w kolumnie "location" narusza ograniczenie wymaganej wartości gdzie samo podzapytanie nie zwraca mi żadnych NULL. jak poprawnie zrobic to zapytanie? -- Pozdrawiam Marcin / nicraM
Witam c.addres to chyba pole string UPDATE netdevices nd SET location = (SELECT c.address from customers c, nodes n WHERE nd.id <http://nd.id> = n.netdev AND c.id <http://c.id> = n.ownerid and c.address not like '' LIMIT 1) lub UPDATE netdevices nd SET location = (SELECT c.address from customers c, nodes n WHERE nd.id <http://nd.id> = n.netdev AND c.id <http://c.id> = n.ownerid and c.address not like ' ' LIMIT 1) On 15.09.2015 13:30, Marcin wrote:
UPDATE netdevices nd SET location = (SELECT c.address from customers c, nodes n WHERE nd.id <http://nd.id> = n.netdev AND c.id <http://c.id> = n.ownerid and c.address IS NOT NULL LIMIT 1)
No tak, to string. dzięki jeszcze tylko musze dorobić case do ewetnualnego adresu instalacji i vuala 2015-09-15 13:44 GMT+02:00 Bartosz Walendziuk <bartek@euro-net.pl>:
Witam c.addres to chyba pole string
UPDATE netdevices nd SET location = (SELECT c.address from customers c, nodes n WHERE nd.id = n.netdev AND c.id = n.ownerid and c.address not like '' LIMIT 1)
lub
UPDATE netdevices nd SET location = (SELECT c.address from customers c, nodes n WHERE nd.id = n.netdev AND c.id = n.ownerid and c.address not like ' ' LIMIT 1)
On 15.09.2015 13:30, Marcin wrote:
UPDATE netdevices nd SET location = (SELECT c.address from customers c, nodes n WHERE nd.id = n.netdev AND c.id = n.ownerid and c.address IS NOT NULL LIMIT 1)
_______________________________________________ lms mailing list lms@lists.lms.org.pl http://lists.lms.org.pl/mailman/listinfo/lms
-- Pozdrawiam Marcin / nicraM
zrobiłem to w ten sposób: UPDATE netdevices nd SET location = COALESCE((SELECT CONCAT(c.city, ', ',c.address) as locat from customers c, nodes n WHERE nd.id = n.netdev AND c.id = n.ownerid LIMIT 1), '') WHERE (name LIKE 'KL%' OR name LIKE 'kl%' OR name LIKE 'Kl%') z tymi like na końcu nie mogłem zrobić w regexp LIKE 'kl%|KL%|Kl%' bo nie znajdował żadnego rekordu :/ 2015-09-15 13:52 GMT+02:00 Marcin <marcin@nicram.net>:
No tak, to string. dzięki jeszcze tylko musze dorobić case do ewetnualnego adresu instalacji i vuala
2015-09-15 13:44 GMT+02:00 Bartosz Walendziuk <bartek@euro-net.pl>:
Witam c.addres to chyba pole string
UPDATE netdevices nd SET location = (SELECT c.address from customers c, nodes n WHERE nd.id = n.netdev AND c.id = n.ownerid and c.address not like '' LIMIT 1)
lub
UPDATE netdevices nd SET location = (SELECT c.address from customers c, nodes n WHERE nd.id = n.netdev AND c.id = n.ownerid and c.address not like ' ' LIMIT 1)
On 15.09.2015 13:30, Marcin wrote:
UPDATE netdevices nd SET location = (SELECT c.address from customers c, nodes n WHERE nd.id = n.netdev AND c.id = n.ownerid and c.address IS NOT NULL LIMIT 1)
_______________________________________________ lms mailing list lms@lists.lms.org.pl http://lists.lms.org.pl/mailman/listinfo/lms
-- Pozdrawiam Marcin / nicraM
-- Pozdrawiam Marcin / nicraM
Można skorzystać z ILIKE i wtedy jest case-insensitive. 2015-09-15 14:13 GMT+02:00 Marcin <marcin@nicram.net>:
zrobiłem to w ten sposób: UPDATE netdevices nd SET location = COALESCE((SELECT CONCAT(c.city, ', ',c.address) as locat from customers c, nodes n WHERE nd.id = n.netdev AND c.id = n.ownerid LIMIT 1), '') WHERE (name LIKE 'KL%' OR name LIKE 'kl%' OR name LIKE 'Kl%')
z tymi like na końcu nie mogłem zrobić w regexp LIKE 'kl%|KL%|Kl%' bo nie znajdował żadnego rekordu :/
2015-09-15 13:52 GMT+02:00 Marcin <marcin@nicram.net>:
No tak, to string. dzięki jeszcze tylko musze dorobić case do ewetnualnego adresu instalacji i vuala
2015-09-15 13:44 GMT+02:00 Bartosz Walendziuk <bartek@euro-net.pl>:
Witam c.addres to chyba pole string
UPDATE netdevices nd SET location = (SELECT c.address from customers c, nodes n WHERE nd.id = n.netdev AND c.id = n.ownerid and c.address not like '' LIMIT 1)
lub
UPDATE netdevices nd SET location = (SELECT c.address from customers c, nodes n WHERE nd.id = n.netdev AND c.id = n.ownerid and c.address not like ' ' LIMIT 1)
On 15.09.2015 13:30, Marcin wrote:
UPDATE netdevices nd SET location = (SELECT c.address from customers c, nodes n WHERE nd.id = n.netdev AND c.id = n.ownerid and c.address IS NOT NULL LIMIT 1)
_______________________________________________ lms mailing list lms@lists.lms.org.pl http://lists.lms.org.pl/mailman/listinfo/lms
-- Pozdrawiam Marcin / nicraM
-- Pozdrawiam Marcin / nicraM
_______________________________________________ lms mailing list lms@lists.lms.org.pl http://lists.lms.org.pl/mailman/listinfo/lms
wykombinowałem cosik takiego: #v+ UPDATE netdevices nd SET location = COALESCE((SELECT CONCAT( CASE WHEN c.post_city != '' THEN c.post_city ELSE c.city END, ', ', CASE WHEN c.post_address != '' THEN c.post_address ELSE c.address END ) as locat from customers c, nodes n WHERE nd.id = n.netdev AND c.id = n.ownerid LIMIT 1), '') WHERE name ~* '^kl*' #v- działa 2015-09-15 14:28 GMT+02:00 Skiba Marek <skibamarek@gmail.com>:
Można skorzystać z ILIKE i wtedy jest case-insensitive.
2015-09-15 14:13 GMT+02:00 Marcin <marcin@nicram.net>:
zrobiłem to w ten sposób: UPDATE netdevices nd SET location = COALESCE((SELECT CONCAT(c.city, ', ',c.address) as locat from customers c, nodes n WHERE nd.id = n.netdev AND c.id = n.ownerid LIMIT 1), '') WHERE (name LIKE 'KL%' OR name LIKE 'kl%' OR name LIKE 'Kl%')
z tymi like na końcu nie mogłem zrobić w regexp LIKE 'kl%|KL%|Kl%' bo nie znajdował żadnego rekordu :/
2015-09-15 13:52 GMT+02:00 Marcin <marcin@nicram.net>:
No tak, to string. dzięki jeszcze tylko musze dorobić case do ewetnualnego adresu instalacji i vuala
2015-09-15 13:44 GMT+02:00 Bartosz Walendziuk <bartek@euro-net.pl>:
Witam c.addres to chyba pole string
UPDATE netdevices nd SET location = (SELECT c.address from customers c, nodes n WHERE nd.id = n.netdev AND c.id = n.ownerid and c.address not like '' LIMIT 1)
lub
UPDATE netdevices nd SET location = (SELECT c.address from customers c, nodes n WHERE nd.id = n.netdev AND c.id = n.ownerid and c.address not like ' ' LIMIT 1)
On 15.09.2015 13:30, Marcin wrote:
UPDATE netdevices nd SET location = (SELECT c.address from customers c, nodes n WHERE nd.id = n.netdev AND c.id = n.ownerid and c.address IS NOT NULL LIMIT 1)
_______________________________________________ lms mailing list lms@lists.lms.org.pl http://lists.lms.org.pl/mailman/listinfo/lms
-- Pozdrawiam Marcin / nicraM
-- Pozdrawiam Marcin / nicraM
_______________________________________________ lms mailing list lms@lists.lms.org.pl http://lists.lms.org.pl/mailman/listinfo/lms
_______________________________________________ lms mailing list lms@lists.lms.org.pl http://lists.lms.org.pl/mailman/listinfo/lms
-- Pozdrawiam Marcin / nicraM
uczestnicy (3)
-
Bartosz Walendziuk -
Marcin -
Skiba Marek