Concise SQL for joining many tables on a single column

Question

I have about 122 tables that all share a particular column. Is there an elegant/concise method to join all of these tables on that column without having 121 instances of

join on A.id = B.id

in the query?

Answer 1

If the column in question has the same name in both tables (which it should) then you can use this shorter syntax:

SELECT ... FROM table1 JOIN table2 USING (column)

The column will also appear only once in the result, instead of being present for each table. More details here.

You will still have to do it for each table, though.

Answer 2

Here goes your solution:

Create table and insert statement:

create table splitUpdate (no int,productname varchar(10),productcrossell varchar(20));
insert into splitUpdate values (1,'a','a(1)');
insert into splitUpdate values (2,null,'c(4),d(5)');
insert into splitUpdate values (3,null,'Z(1),b(2)');

create table eleminate (product varchar(20));
insert into eleminate values('x');
insert into eleminate values('y');
insert into eleminate values('Z');
insert into eleminate values('z');

Update Query:

with cte as (
    select no,productname,p.product,row_number()over(partition by no)rn ,substring(p.product  from 1 for position('(' in p.product )-1) SplittedProduct
from splitupdate t, unnest(string_to_array(t.productcrossell ,','))p(product)
where substring(p.product  from 1 for position('(' in p.product )-1) not in (select product from eleminate))
update splitupdate set productname=splittedproduct 
from cte 
where splitupdate.productname is null and splitupdate.no=cte.no and cte.rn=1 

SplitUpdate Table before updating:

|no|productname|productcrossell| 
|1 |a          |a(1)           |
|2 |c          |c(4),d(5)      |
|3 |b          |Z(1),b(2)      |

Result:

|no|productname|productcrossell| 
|1 |a          |a(1)           |
|2 |c          |c(4),d(5)      |
|3 |b          |Z(1),b(2)      |