SQL - Read For Learn

What is the difference between Scope_Identity(), Identity(), @@Identity, and Ident_Current()?

The @@identity function returns the last identity created in the same session. The scope_identity() function returns the last identity created in the same session and the same scope. The ident_current(name) returns the last identity created for a specific table or view in any session. The identity() function is not used to get an identity, it’s used to create an identity in a select…into query. The … Read more

Warning: Null value is eliminated by an aggregate or other SET operation in Aqua Data Studio

You would mostly be using COUNT to summarize over a UID. Therefore COUNT([uid]) will produce the warning: Warning: Null value is eliminated by an aggregate or other SET operation. whilst being used with a left join, where the counted object does not exist. Using COUNT(*) in this case would also render incorrect results, as you would then be counting the … Read more

ORA-00972 identifier is too long alias column name

No, prior to Oracle version 12.2, identifiers are not allowed to exceed 30 characters in length. See the Oracle SQL Language Reference. However, from version 12.2 they can be up to 128 bytes long. (Note: bytes, not characters).

Get day of week in SQL Server 2005/2008

Use DATENAME or DATEPART:

SQL join on multiple columns in same tables

I have 2 subqueries, but I’m having trouble joining columns together from the same tables. I tried: If I simply end the query with ON a.userid = b.sourceid it works, but how can I also join these tables on another column also ON a.listid = b.destinationid ?? Any help appreciated.

MySQL syntax for Join Update

MySQL supports a multi-table UPDATE syntax, which would look approximately like this: You can update the Train table and delete from the Reservations table in the same transaction. As long as you do the update first and then do the delete second, it should work.

“CASE” statement within “WHERE” clause in SQL Server 2008

I am working with a query which contains “CASE” statement within “WHERE” clause. But SQL Server 2008 is giving some errors while executing it. Can anyone please help me with the correct query? Here is the query:

The multi-part identifier could not be bound

You are mixing implicit joins with explicit joins. That is allowed, but you need to be aware of how to do that properly. The thing is, explicit joins (the ones that are implemented using the JOIN keyword) take precedence over implicit ones (the ‘comma’ joins, where the join condition is specified in the WHERE clause). Here’s an outline of … Read more

Error Code: 2013. Lost connection to MySQL server during query

New versions of MySQL WorkBench have an option to change specific timeouts. For me it was under Edit → Preferences → SQL Editor → DBMS connection read time out (in seconds): 600 Changed the value to 6000. Also unchecked limit rows as putting a limit in every time I want to search the whole data … Read more

MySQL Multiple Joins in one query?

You can simply add another join like this: However be aware that, because it is an INNER JOIN, if you have a message without an image, the entire row will be skipped. If this is a possibility, you may want to do a LEFT OUTER JOIN which will return all your dashboard messages and an image_filename only if … Read more

+ More