If you are trying to improve performance, you could try creating a persisted computed column on either a hash or concatenated value of the two columns. Once it is persisted, provided the column is deterministic and you are using “sane” database settings, it can be indexed and / or statistics can be created on it. … Read more
Use INSERT … SELECT: insert into your_table (c1, c2, …) select c1, c2, … from your_table where id = 1 where c1, c2, … are all the columns except id. If you want to explicitly insert with an id of 2 then include that in your INSERT column list and your SELECT: insert into your_table … Read more
You can do this: sql = “Select * from … your sql query here” records_array = ActiveRecord::Base.connection.execute(sql) records_array would then be the result of your sql query in an array which you can iterate through.
Try: With DependencedIncidents AS ( SELECT INC.[RecTime],INC.[SQL] AS [str] FROM ( SELECT A.[RecTime] As [RecTime],X.[SQL] As [SQL] FROM [EventView] AS A CROSS JOIN [Incident] AS X WHERE patindex(‘%’ + A.[Col] + ‘%’, X.[SQL]) > 0 ) AS INC ), lalala AS ( SELECT INC.[RecTime],INC.[SQL] AS [str] FROM ( SELECT A.[RecTime] As [RecTime],X.[SQL] As [SQL] FROM … Read more
Aggregate the column by COUNT, then use a HAVING clause to find values that appear greater than one time. SELECT column_name, COUNT(column_name) FROM table_name GROUP BY column_name HAVING COUNT(column_name) > 1;
If you only want to match “” as an empty string WHERE DATALENGTH(COLUMN) > 0 If you want to count any string consisting entirely of spaces as empty WHERE COLUMN <> ” Both of these will not return NULL values when used in a WHERE clause. As NULL will evaluate as UNKNOWN for these rather … Read more
In addition to this answer if you need to change tableB.value according to tableA.value dynamically you can do for example: UPDATE tableB INNER JOIN tableA ON tableB.name = tableA.name SET tableB.value = IF(tableA.value > 0, tableA.value, tableB.value) WHERE tableA.name=”Joe”
If the list of conditions is not known at compile time and is instead built at run time, you don’t have to worry about whether you have one or more than one condition. You can generate them all like: and <condition> and concatenate them all together. With the 1=1 at the start, the initial and … Read more
You didn’t say what version you were using, but in SQL 2005 and above, you can use a common table expression with the OVER Clause. It goes a little something like this: WITH cte AS ( SELECT[foo], [bar], row_number() OVER(PARTITION BY foo, bar ORDER BY baz) AS [rn] FROM TABLE ) DELETE cte WHERE [rn] … Read more
CREATE TABLE someTable ( id serial PRIMARY KEY, col1 int NOT NULL, col2 int NOT NULL, UNIQUE (col1, col2) ) autoincrement is not postgresql. You want a integer primary key generated always as identity (or serial if you use PG 9 or lower. serial was soft-deprecated in PG 10). If col1 and col2 make a … Read more