Your query should go like this: INSERT INTO newDatabase.table1 (Column1, Column2) SELECT column1, column2 FROM oldDatabase.table1; UPDATE Since this answer is getting more attention than I even anticipated, I should expand on this answer. First of all, it might not be obvious from the answer itself, but the columns do not need to have the … Read more
sql execution order: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT . SQL Query mainly works in three phases . 1) Row filtering – Phase 1: Row filtering – phase 1 are done by FROM, WHERE , GROUP BY , HAVING clause. 2) Column filtering: Columns … Read more
Create an array from the two columns, the aggregate the array: select id, array_agg(array[x,y]) from the_table group by id; Note that the default text representation of arrays uses curly braces ( {..}) not square brackets ([..])
SQL was designed as a declarative language, in sense that you tell what you want to get and the SQL engine decides how. However, SQL operates on sets, and the results of the functions can be first class sets in Oracle, SQL Server and PostgreSQL. One can say that SQL is functional language, as long … Read more
You could use windowed COUNT: SELECT sub.name, sub.employee_id FROM (SELECT *, COUNT(*) OVER(PARTITION BY employee_id) AS c FROM users) AS sub WHERE c > 1; LiveDemo or simple IN: SELECT * FROM users WHERE employee_id IN (SELECT employee_id FROM users GROUP BY employee_id HAVING COUNT(employee_id) > 1); LiveDemo2 or correlated subquery: SELECT name, employee_id FROM … Read more
you can use common table expression for this: ;with cte as ( select top (@MaxRecords) status from Messages where Status=”N” and InsertDate >= getdate() order by … ) update cte set status=”P” output inserted.* This one uses the fact that in SQL Server it’s possible to update cte, like updatable view.
You can accomplish this using CAST(0 as BIT) update TestTable set jsonData = JSON_MODIFY(jsonData, ‘$.isActive’, CAST(0 as BIT)) If you want to set it to true, it is simply CAST(1 as BIT) instead. This works because in SQL, a boolean is represented as a BIT, which can only be 0 or 1. In its translation … Read more
You need to add WITH (NOLOCK). Not entirely sure why but I just ran into this issue today and this solved my issue. SELECT * FROM [110.10.10.100].testdbname.dbo.ufn_getdata(‘4/25/2013’) AS tb WITH (NOLOCK);
Add the length to your String column: src_address = Column(String(16), index=True)