Search code examples
sqlif-statementwhere-clause

Change rows based on condition

I got a table with a country column and each country has multiple rows.

I want to query rows where seq_no is equal to 1. However, if the country is USA or Canada, I want the seq_no to be equal to 2, something like this:

SELECT language
FROM table
WHERE IF(country = 'USA' OR 'Canada')
      THEN (seq_no = '2')
      ELSE (seq_no = '1')
    ENDIF;

Is that possible?

Solution

  • If you need to apply different condition on column based on another column, you can use case in you where clause. For example:

    SELECT *
FROM table
WHERE seq_no = CASE WHEN country in ('USA', 'Canada')
      THEN '2'
      ELSE '1' END;

    Input:

    country seq_no
    USA 2
    Canada 1
    Other 1

    Output:

    country seq_no
    USA 2
    Other 1

    Demo can be seen here.