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