Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am working on complicated problem, but I will simplify it to this problem.

I have two tables

A [ID, column1, column2, column3]
B [ID, column1, column2, column3, column4]

and i want to update the third:

C [ID, column1, column2]

I am writing a query to

Select A.column1, B.column1 from A,B where  (A.column1 -B.column1)=1 limit 1;

Now I want to get all the points that

insert C(column1, column2)
Select A.column1, secElement from (Select A.column1 firstElement, B.column1 secElement from A,B where  (A.column3 -B.column3)=1 limit 1) as ex, A where A.column1=ex.firstElement limit 5;

But I am getting just one row.

I aspire to get a result like that.

  • Loop on A

  • for every element in A, I should find its peer.

But I couldn't find a way to do the condition. Take the element, look for its peer then go to the other element.

A.column1 | secElement

25033419 | 244959
25033420 | 244960
25033421 | 244961
25033422 | 244962
25033424 | 244963 (5 rows)

I can express it algorithmically :

Foreach (element e in A)   //Global Query 
  Find_Peer(e)             // Query 1

UPDATE: I'm not in syntax. I am trying to get adapted to it. but it should be something like that.

CREATE OR REPLACE FUNCTION PointPeer(elem integer) RETURNS text AS $$
Select A.column1, B.column1 from A,B where  (A.column1 -B.column1)=1 where  A.ID=elem limit 1::text
$$ LANGUAGE sql;

Select A.ID, PointPeer(A.ID) from A limit 5;

I still struggling with the Syntax.

share|improve this question
Huh? Try to set this out step by step, explaining what your first query is supposed to be doing, what you define as an element's "peer", etc. – Craig Ringer Feb 13 '14 at 0:50
In SQL, that's normally called a JOIN. Look up JOIN in a basic SQL book or web site training like w3school or something. – ETL Feb 13 '14 at 2:21
No ! It's not Join. I need to work on something similar to this (…). Join give only one one record where we do the condition. – user3001937 Feb 13 '14 at 2:37
Check the UPDATE – user3001937 Feb 13 '14 at 2:43
Please remove some noise, only leave what's helping to define the problem. Will it be (A.column3 -B.column3) or (A.column1 -B.column1) as condition? 5 rows per elem in A or 5 rows per or all peers? If just 5, how to pick? Please provide relevant table definitions including UNIQUE and NOT NULL constraints - what \d tbl gives you in psql. The syntax won't be a problem as soon as the job is defined in a way we can understand. – Erwin Brandstetter Feb 13 '14 at 14:39

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.