dimanche 18 novembre 2018

Check there there exists only one null in each row, between two columns MySql (version 8.0.13)

I have two columns col1 and col2 under table name table. Ideally, the data in the table should look like:

 col1 | col2
  A     null
  null   B
  null   A
  C      null

The data would have an error if there exists a row in which both columns are null or if both columns have a value. So for example if there is a row that looks like

 col1| col2
  A     B

or

col1 | col2
 null   null

I can count the number of nulls in each column by using a query like select count(*) from table where col1 IS NULL select count(*) from table where col2 IS NULL

However, just knowing the counts of the nulls and adding them up does not tell me where the nulls actually exist in the table. Is there a query that I can write to test if there exists only one null in each row?

Aucun commentaire:

Enregistrer un commentaire