Incorrect syntax near ”
Such unexpected problems can appear when you copy the code from a web page or email and the text contains unprintable characters like individual CR or LF and non-breaking spaces.
SQL
How to delete from multiple tables in MySQL?
Use a JOIN in the DELETE statement. Alternatively you can use… …to delete only from pets_activities See this. For single table deletes, yet with referential integrity, there are other ways of doing with EXISTS, NOT EXISTS, IN, NOT IN and etc. But the one above where you specify from which tables to delete with an … Read more
SQL Server dynamic PIVOT query?
Dynamic SQL PIVOT: Results:
PostgreSQL create table if not exists
This feature has been implemented in Postgres 9.1: For older versions, here is a function to work around it: Call: Notes The columns schemaname and tablename in pg_tables are case-sensitive. If you double-quote identifiers in the CREATE TABLE statement, you need to use the exact same spelling. If you don’t, you need to use lower-case strings. See: Are PostgreSQL column names case-sensitive? pg_tables only contains actual tables. The … Read more
Replacing NULL with 0 in a SQL server query
When you want to replace a possibly null column with something else, use IsNull. This will put a 0 in myColumn if it is null in the first place.
Errors: “INSERT EXEC statement cannot be nested.” and “Cannot use the ROLLBACK statement within an INSERT-EXEC statement.” How to solve this?
This is a common issue when attempting to ‘bubble’ up data from a chain of stored procedures. A restriction in SQL Server is you can only have one INSERT-EXEC active at a time. I recommend looking at How to Share Data Between Stored Procedures which is a very thorough article on patterns to work around … Read more
Is it possible to specify condition in Count()?
If you can’t just limit the query itself with a where clause, you can use the fact that the count aggregate only counts the non-null values: You can also use the sum aggregate in a similar way:
what is the difference between triggers, assertions and checks (in database)
Triggers – a trigger is a piece of SQL to execute either before or after an update, insert, or delete in a database. An example of a trigger in plain English might be something like: before updating a customer record, save a copy of the current record. Which would look something like: The difference between … Read more
‘CREATE PROCEDURE’ must be the only statement in the batch (Erro)
The error is self-explanatory – you cannot issue a CREATE PROCEDURE statement unless it’s the only statement in the batch. In SSMS the GO keyword splits the statement into separate batches, so you need to add one after the statement before the CREATE PROCEDURE:
Can I use multiple “with”?
Try: And yes, you can reference common table expression inside common table expression definition. Even recursively. Which leads to some very neat tricks.