Delete something that is not in something in SQL

sql delete from multiple tables
delete where not in mysql
delete in sql
sql not in
sql select and delete at the same time
sql delete from select
sql if then delete
delete not in abap

I have two result sets, one is:

select * from tgCity

Which has about 850 records. It has cityID and city name.

The other result set is:

select max(cityID), city 
from tgCity 
group by City

This returns about 700 records. The reason is that in the 850 there are 150 duplicated records of some cities inside the 700 records.

How can I write a script to delete in the tgCity table that only deletes those 150 records?

Here is another classic solution using a WHERE EXISTS condition :

DELETE t
FROM tgCity t
WHERE EXISTS (
    SELECT 1 FROM tgCity t1 WHERE t1.city = t.city AND t1.cityId < t.cityId
)

This will delete records having the same city while keeping the one with the smallest cityId in the group.

Demo on DB Fiddle

How to Delete Records NOT IN, The next thing is now to delete all Schemes which have an id of NULL or an id which does not exists in the ProjectsSchemes Table: DELETE� DELETE Syntax. DELETE FROM table_name WHERE condition; Note: Be careful when deleting records in a table! Notice the WHERE clause in the DELETE statement. The WHERE clause specifies which record (s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!

Assuming CityId is unique, a common method is:

delete from tgCity tg
    where tg.cityId < (select tg2.cityId
                       from tgCity tg2
                       where tg2.city = tg.city
                      );

SQL: DELETE Statement, Oops, something seems to be preventing the display of our advertisements. This website would Duration: 2:17 Posted: 3 Jan 2008 The SQL NOT condition (sometimes called the NOT Operator) is used to negate a condition in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement.

This script deletes duplicate cities:

Delete c2 from tgCity c1 join tgCity c2
On c1.city=c2.city
Where c2.city_id>c1.city_id

SQL DELETE Statement, Note: Be careful when deleting records in a table! Notice the WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) should be � You can use the NOT IN statement like described here http://www.techonthenet.com/sql/in.php Basically you would write a select query from the table where you would like to delete records from and then perform a NOT IN on a subquery that joins the two tables.

SQL DELETE Statement, Keep up the pace for the next section, in which we'll show you something with which you must be very careful. Unsafe Delete Operation. If we do not set a� The NOT operator works with all of your SQL keywords to negate results. Think of the NOT operator as a cancellation phrase. When you use the IN operator, the SQL language first gets all of the values that match. In this case, the parameters are any record that starts with the characters "da." SQL then sees the NOT operator, and eliminates the results.

SQL DELETE - Deleting Data in a Table or Multiple Tables, SQL DELETE – deleting all rows in a table. To remove all rows in the employees table, you just execute the following query: (not recommended and make a� WHERE Phone NOT LIKE '503%' This example returns all the rows in the table for which the phone number starts with something other than 503. You may want to search for a string that includes an actual percent sign or underscore. In that case, you want SQL to interpret the percent sign as a percent sign and not as a wildcard character.

How to Delete in MySQL, Works for PostgreSQL, MySQL, BigQuery, SQL Server, Redshift, Snowflake, SQLite, Presto, Cassandra, Oracle, ODBC, Panoply, MongoDB, Athena, and more� Performance consideration of SQL Not Equal operator. In this part, we will explore the performance consideration of SQL Not Equal operator. For this part, let’s keep only 10 records in the products table. It helps to demonstrate the situation quickly. Execute the following query to delete products having ProductID>10.

Comments
  • Tag your question with the database that you are using.
  • @GordonLinoff I said, MSSQL is that the reason you -1?
  • . . I don't downvote, so that wouldn't be me.
  • SOrry, where is tg2 coming from?
  • @ZechengLi . . . It is a table alias.