Search code examples
sqlsql-delete

SQL delete from a table where a value is true in other table

I need to write an sql with the below requirements..

  1. I have a table A with column AA in it.
  2. I have another table B with column BB in it.
  3. Column A in table AA will have only two values ..
    either 0 or 100 and column column BB in table B has only one value either 1 or 2 or 3....or 7 and this value represents the daynumber in a week and so will change daily in order or Monday(value=0) to Sunday(value=7)..Please ignore the content in comments./* BB in table b has values from 1 through 7.*/
    There is no column common between A and B tables

Now, I need to delete table A where AA = 100... but this should happen only when column BB = 7.

Can anyone help me out to write SQL for the above.

Solution

  • The statement below will delete all rows from a where aa is equal to 100, only if there exists one or more rows in b where bb is equal to 7.

    delete from a where aa = 100 and exists (select * from b where bb = 7);