How to delete rows from a table by another table with a special condition?

Question

If I have those two tables:

Planet(Name,size)

Moon(Name, PlanetName*)

How can I delete planets that have more than 3 moons? I not sure if my answer is correct or not

DELETE Name FROM Planet INNER JOIN Moon WHERE Moon >3

Gordon Linoff · Accepted Answer · 2018-01-13 01:44:13Z

Your answer is not correct. The planets that have more than three moons are given by:

select planetname from moon group by panetname having count(*) > 3;

You can then delete the planets using in or exists:

delete from planet where planetname in (select planetname from moon group by planetname having count(*) > 3 );

Martin · Accepted Answer · 2018-01-13 01:47:23Z

delete from planets where (select count(*) from moon where moon.planetname = planet.name) > 3

or

delete from planets where exists(select count(*) from moon where moon.planetname = planet.name group by planetname -- you may not need that, because for each row they are all equal, due to the where condition having count(*) > 3 )

Usman · Accepted Answer · 2018-01-13 08:38:16Z

Your query is wrong ! because your query donot mention the count .

Try this query

DELETE from planets WHERE Name = ((SELECT COUNT(*) FROM moon WHERE moon.planetname = planet.name) > 3 )

Collectives™ on Stack Overflow

How to delete rows from a table by another table with a special condition?

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related