PostgreSQL Finding IDs that don't exist in two other tables -


i want change io_dl field 1 0 in io table, if 3 conditions hold true

io table pkey -> io_id

  1. the pkey io not in entire table m fkey named m_id
  2. the pkey io not contained in table p, column p_id csv delimited string of ids ex. "1923,2309,210"
  3. the current value io_dl field in io table set 1

step 3 guess isn't needed setting 0 0 doesn't mess up, check may slow query down?

this i've tried , im getting massive list of same io_ids listed, think i'm using join or union wrongly.

update io set io_dl = 0 io_id in (     select i.io_id io         inner join (             select p_id "io_id" p             union             select regexp_split_to_table(m.m_id, ',')::integer             id m             ) q         on i.io_id != q.io_id     i.io_dl = 1 );

made own solution few minutes later simpler query believe.

update io set io_dl = 0 io_id in (     select i.io_id io     i.io_id not in (         select p_id "io_id" p         union         select regexp_split_to_table(m.m_id, ',')::integer "io_id" m     ) , i.io_dl = 1 );

use except distinct ids:

update io set io_dl = 0 io_id in (     select io_id io     except (         select p_id p         union         select regexp_split_to_table(m_id, ',')::integer m     ) ) , io_dl = 1 returning *;

by adding returning * can control how many rows updated.


-

Popular posts from this blog

Apache NiFi ExecuteScript: Groovy script to replace Json values via a mapping file -

i working apache nifi 0.5.1 on groovy script replace incoming json values ones contained in mapping file. mapping file looks (it simple .txt): header1;header2;header3 a;some text;a2 i have started following: import groovy.json.jsonbuilder import groovy.json.jsonslurper import java.nio.charset.standardcharsets def flowfile = session.get(); if (flowfile == null) { return; } flowfile = session.write(flowfile, { inputstream, outputstream -> def content = """ { "field1": "a" "field2": "a", "field3": "a" }""" def slurped = new jsonslurper().parsetext(content) def builder = new jsonbuilder(slurped) builder.content.field1 = "a" builder.content.field2 = "some text" builder.content.field3 = "a2" outputstream.write(builder.toprettystring().ge...
Read more

node.js - How do I prevent MongoDB replica set from querying the primary? -

read-operations-only: is there way have mongodb replica set, make mongodb instance on box connected to, mongodb gets queried? i have 3 ec2 instances behind aws load balancer. on each ec2 instance runs mongodb, part of replica set. i have express endpoints on nodejs , connect replica set follows mongodb.mongoclient.connect('mongodb://1.1.1.1,2.2.2.2,3.3.3.3.', function (err, db) { if (err) { complete('{}'); } else { i distribute load of queries evenly across 3 instances of mongodb replica set instead of default having queries routed ec2 instance primary mongodb defined, because destroys point of load balancer (which balance load of queries across 3 instances evenly). my understanding when connecting mongodb replica set, primary instance selected unless primary instance down, , therefore purpose of every secondary instance serve backup. in example people call hot,cold,cold. because there 2...
Read more