CODEWITHSUNDEEP
sqlpostgresql
telex-wap
telex-wap

Reputation: 852

Right syntax to do an INSERT in PostgreSQL with multiple IF conditions

I have a quite large table of sporting events and I am cleaning up trying to arrange everything in smaller and cleaner tables. I am getting there, but I found an obstacle. This is the original table structure: 

date  |  team_name  |  team_id  |  opposition  |  opposition_id  |  venue
-------------------------------------------------------------------------

and here is what I want to do with it. A part of what I will write now is pseudocode, because I am not very sure how can I express this in PostgreSQL: 

INSERT INTO new_table(date, home, away)
SELECT DISTINCT date, (...)

For the next two fields after date, I want to insert in the column home of the new table the values of the original column team_id IF the value in the original column venue is "home". If the value of venue is not "home", then in the new column home of the new table I will insert the values of the original opposition_id.

Can this be done easily? I found a question dealing with multiple IFs in MySQL here, but I am not very sure about how would I adapt that method to my case.

Upvotes: 1

Views: 39

Answers (1)

Andomar
Andomar

Reputation: 238176

You can use case:

insert  into NewTable
        (date, home)
select  date
,       case venue 
        when 'home' then team_id
        else opposition_id
        end
from    YourTable

Upvotes: 2

Related Questions