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?
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
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
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
uczestnicy (3)
-
Bartosz Walendziuk -
Marcin -
Skiba Marek