CODEWITHSUNDEEP
sqlpostgresql
Shani P.
Shani P.

Reputation: 37

Select only if 2 or more columns match - postgresql

I'm learning SQL on my own and I'm quite new to coding. I need help with a script I'm writing that selects data from a few tables to insert it into another table that already has data. However, I only want to insert the data into the second table if 2 of the 4 columns DON'T match.

Here is an example of the data from the select before I insert (table1):

warehouse   │  section    │   division   │  division_code

  1       │   10   │   1     │   BOXES
  1       │   11   │   1     │   CRATES
  1       │   12   │   1     │   LANES
  2       │   3    │   1     │   OFFICE

Here is an example of the data in the table I want to insert into (table2):

warehouse   │  section    │   division   │  division_code

  1       │   1    │   1     │   BOXES
  1       │   2    │   1     │   LANES
  1       │   3    │   1     │   FUSES
  1       │   4    │   1     │   OFFICE
  2       │   1    │   1     │   LANES
  2       │   2    │   1     │   CRATES

I only want to insert the rows from table 1 into table 2 where the combination of warehouse and division_code columns doesn't exits. The two rows in the case being:

  1       │   11   │   1     │   CRATES
  2       │   3    │   1     │   OFFICE

I tried with EXCEPT but this doesn't work given that the section column is different and I'm not sure how to use NOT IN or NOT EXISTS in this case given the 2 columns that need to be checked.

Any help is greatly appreciated! Thanks!

Upvotes: 2

Views: 2137

Answers (3)

Diego Victor de Jesus
Diego Victor de Jesus

Reputation: 3003

Try this:

INSERT INTO table2
    SELECT * FROM table1 t1
    LEFT JOIN table2 t2
    ON t1.warehouse = t2.warehouse
    AND t1.division_code = t2.division_code
    --Any column
    WHERE t2.division_code IS NULL

Upvotes: 1

Abelisto
Abelisto

Reputation: 15614

One of benefits of SQL is that you can to express your requirement using it in almost same way.

I only want to insert the rows from table 1 into table 2 where the combination of warehouse and division_code columns doesn't exits.

-- I only want to insert the rows from table 1 into table 2 ...
insert into table2
select * from table1
-- where ... doesn't exits
where not exists (
  select 1 
  from table2
  -- the combination of warehouse and division_code columns
  where
    table2.warehouse = table1.warehouse and
    table2.division_code = table1.division_code)

In PostgreSQL you can to do it in even more convenient way using row syntax:

insert into table2
select * from table1
where (table1.warehouse, table1.division_code) not in (
  select table2.warehouse, table2.division_code from table2)

Upvotes: 1

VDALLCO
VDALLCO

Reputation: 166

declare @count int
select * into #tmp from table1

while exists (select * from #tmp)
begin


IF NOT EXISTS(SELECT 1 FROM table1 t1 left join table2 t2 on t1.warehouse=t2.warehouse and t1.division_code=t2.division_code)
    INSERT INTO table2
    select * from t1 where PrimaryKeyID=@count

delete from #tmp where PrimaryKeyID=@count
end 
drop table #tmp

You'll need a primary integer key on table1 if you don't already (for this solution). Keep in mind I didn't test this so you'll need to modify it. I'm also somewhat new to SQL. This solution could be entirely wrong and non-functional, you'll have to check for yourself.

The basic understanding is that:

  • You load the table1 into a temp table
  • Loop over that temp table
  • Use NOT EXIST with a select on table1 left joined to table2.
  • If the 2 columns don't exist in table 2, insert them right from table1.

Upvotes: -1

Related Questions