How Important is it to avoid nested queries. I have always learnt to avoid them like a plague. But they are the most natural thing to me. When I am designing a query, the first thing I write is a nested query. Then I convert it to joins, which sometimes takes a lot of time to get right. And rarely
Tag: sql
is it possible to select EXISTS directly as a bit?
I was wondering if it’s possible to do something like this (which doesn’t work): select cast( (exists(select * from theTable where theColumn like ‘theValue%’) as bit) Seems like it should be doable, but lots of things that should work in SQL don’t 😉 I’ve seen workarounds fo…
Swap values for two rows in the same table in SQL Server
I want to swap the values from two rows in a table. I have the rows IDs of the two rows. Is there any query to do that? Here is an example. Before the query I have this: row1 : 1,2,3 row2 : 5,6,7 After the swap I want this: row1 : 5,6,7 row2 : 1,2,3 Answer If you want
Why do I get “Procedure expects parameter ‘@statement’ of type ‘ntext/nchar/nvarchar’.” when I try to use sp_executesql?
Why do I get this error Procedure expects parameter ‘@statement’ of type ‘ntext/nchar/nvarchar’. when I try to use sp_executesql? Answer Sounds like you’re calling sp_executesql with a VARCHAR statement, when it needs to be NVARCHAR. e.g. This will give the error because @SQL nee…
SQL How to correctly set a date variable value and use it?
I have the following query which uses a date variable, which is generated inside the stored procedure: The problem is that the @sp_Date value appears to be being ignored and I am wondering why? Have I defined or used it incorrectly? Answer Your syntax is fine, it will return rows where LastAdDate lies within …
How to find Expr#### in Execution Plan
When looking at the actual execution plan for a query in SQL Server Management Studio (SSMS), how do I determine what an expression such as Expr1052 represents? When I identify the costly parts of the query and look at the properties of that operation, there are often references only to these Expressions, or …
MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query
I have a SQL query where I want to insert multiple rows in single query. so I used something like: The problem is when I execute this query, I want to check whether a UNIQUE key (which is not the PRIMARY KEY), e.g. ‘name’ above, should be checked and if such a ‘name’ already exists, th…
can we list all tables in msaccess database using sql?
Can we find all tables in the msaccess using sql . as we do in sqlserver in sqlite Answer Use MSysObjects
Sql select rows containing part of string
I want to write a comparation procedure (t-sql) for site seo. I have a table with field ‘url’ (nvarchar()) that contain a part of site url’s. Ex: ‘mysyte.com/?id=2’. Also this table for each url contains metadata, that i need to extract. The main problem is that full url on site …
How to delete from a table where ID is in a list of IDs?
if I have a list of IDs (1,4,6,7) and a db table where I want to delete all records where ID is in this list, what is the way to do that? Answer Your question almost spells the SQL for this: