I have a table that is missing a column in its primary key constraint. Instead of editing it through SQL Server, I want to put this in a script to add it as part of our update scripts. What syntax can I use to do this? Must I drop and recreate the key constraint? Answer Yes. The only way would
Tag: sql
PostgreSQL unnest() with element number
When I have a column with separated values, I can use the unnest() function: How can I include element numbers? I.e.: I want the original position of each element in the source string. I’ve tried with window functions (row_number(), rank() etc.) but I always get 1. Maybe because they are in the same row…
Showing what quarter of a financial year a date is in
I’m trying to construct a query that will map two columns, one, a date from a table, the second column an alias to show what quarter and financial year the date falls into. Unfortunately I don’t have enough knowledge of SQL to know where to begin. I know that I’d do this with a combination o…
SQL: Return only first occurrence
I seldomly use SQL and I cannot find anything similar in my archive so I’m asking this simple query question: I need a query which one returns personID and only the first seenTime Records: Wanted result: That’s what I did & failed: P.S: Notice SQL CE 4 Answer If your seenTime increases as seen…
Rename a constraint in SQL Server?
Is it possible to rename a constraint in SQL Server? I don’t want to have to delete and create a new one because this constraint affects other already existing constraints and I will have to recreate/alter those. Answer You can rename using sp_rename using @objtype = ‘OBJECT’ This works on o…
Difference between natural join and inner join
What is the difference between a natural join and an inner join? Answer One significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned. Consider: The INNER JOIN of TableA and TableB on Column1 will return The NATURAL JOIN of TableA and TableB on Column1 will return: The repea…
Exit the SQLite shell on Android
I’m using SQLite 3 on Mac OS X’s ADB shell to view my application database using: After I wrongly typed the quit command above, instead of the .quit for example, the shell goes into the …> prompt where none of the known commands seem to work. I can’t even get out by Ctrl + C, Ctrl +…
Get number of partitions in PostgreSQL database
What is the most efficient way to get the number of partitions created in the database? I am using PostgreSQL API for C++. Answer This is how you can select all the names of the table partitions: It can be used to count as well:
What is better to return single value from stored procedure to .Net: OUTPUT parameter or ExecuteScalar?
I need to create a stored procedure that needs to return a count of some records. I’m using .Net to read the result. I can use an OUTPUT parameter to return the value or I could do a select count(*) …
Multiple LEFT JOIN in Access
I have the following query, which works for MySQL: But it doesn’t work for MS Access. I’ve tried to add parentheses around the LEFT JOIN, but it gives me syntax error in FROM clause. So how should this query look in order to work in MS Access? Answer The Access DELETE requires a star (*): DELETE *…