Database

How to convert a string to date in MySQL?

As was told at MySQL Using a string column with date text as a date field, you can do You can also handle these date strings in WHERE clauses. For example You can handle all kinds of date/time layouts this way. Please refer to the format specifiers for the DATE_FORMAT() function to see what you … Read more

How to SUM and SUBTRACT using SQL?

I think this is what you’re looking for. NEW_BAL is the sum of QTYs subtracted from the balance: If you want to update the item balance with the new balance, use the following: This assumes you posted the subtraction backward; it subtracts the quantities in the order from the balance, which makes the most sense … Read more

MySQL AS keyword

I’m not professional in query writing, but wrote many from the time I began MySQL. Recently I noticed there is no need to type AS keyword in name aliasing. equals to However I know that this ability is out from years ago. I’ve 2 questions around this subject: Is AS keyword redundant? Sometimes ago when … Read more

Remove duplicate rows in MySQL

A really easy way to do this is to add a UNIQUE index on the 3 columns. When you write the ALTER statement, include the IGNORE keyword. Like so: This will drop all the duplicate rows. As an added benefit, future INSERTs that are duplicates will error out. As always, you may want to take a backup before running something like this…

What is the Oracle equivalent of SQL Server’s IsNull() function?

coalesce is supported in both Oracle and SQL Server and serves essentially the same function as nvl and isnull. (There are some important differences, coalesce can take an arbitrary number of arguments, and returns the first non-null one. The return type for isnull matches the type of the first argument, that is not true for coalesce, at least on SQL Server.)

Count(*) vs Count(1) – SQL Server

There is no difference. Reason: Books on-line says “COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )“ “1” is a non-null expression: so it’s the same as COUNT(*). The optimizer recognizes it for what it is: trivial. The same as EXISTS (SELECT * … or EXISTS (SELECT 1 … Example: Same IO, same plan, … Read more

+ More