CODEWITHSUNDEEP
sqlsql-delete
Kh Ab
Kh Ab

Reputation: 1

The delete statement SQL

How can I write this?

A delete statement to remove all records from the TableA table where the LastName starts with ba and they have a phone number in the 3 area code.

DELETE
FROM TableA
WHERE LastName = 'ba' AND PhoneNumber = '3';

Upvotes: 0

Views: 69

Answers (2)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131189

MySQL, SQL Server, PostgreSQL, SQLite are all completely different database products. In all of them though, matching a pattern is done using LIKE, not =. To match a value that starts with a certain prefix you use LIKE 'something%'

% matches any character. It's the equivalent of * in a file search or .* in a regular expression.

DELETE FROM TableA 
WHERE LastName LIKE 'ba%' AND PhoneNumber LIKE '3%'

Different databases have different case-sensitivity (collation) rules though.

In SQL Server and MySQL, case-insensitive sorting and searching is most common, so LIKE 'ba%' will match both Ba and ba.

In PostgreSQL, the norm is case-sensitive matching. You may have to use [Bb]a% in that case, to match either B or b

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

Assuming the phone number column be text, then we can use LIKE comparisons as follows:

DELETE
FROM TableA
WHERE LastName LIKE 'Ba%' OR LastName LIKE 'ba%' OR PhoneNumber LIKE '3%';

Upvotes: 1

Related Questions