Advertisements I am trying to make a simple SQL query in PostgreSQL to use in our team: with findData as ( select ‘(”XXX”,”YYYY”)’ as dataList ) select * from findData That gives the result: (‘XXX’,’YYYY’) Exactly what I wanted ! But I want to use it like this in a simple SQL script: select t.variabel1,… Read More Use a string value from a CTE in an IN-clause in a simple SQL query

Advertisements I am trying to write a query where I select records by some condition, update one column of those records and then return them, all in one transaction. I have tried two options. First with CTE: BEGIN; WITH unwatched_rows AS ( SELECT * from my_table WHERE is_readed = false FOR UPDATE ) UPDATE my_table… Read More Error after second read from CTE or TEMPORARY TABLE in Postgres

Advertisements I have an INSERT statement where I want to copy contents of one table to the other, and want to write the number of rows inserted into a log table. I tried this: with upd as ( insert into tb1 OVERRIDING USER VALUE select * from tb2 returning count(*) as num_rows_inserted ) insert into… Read More Return number of rows inserted and insert into log table

Advertisements Here’s my code, to traverse all nodes starting from one: SELECT * INTO MyTable FROM ( SELECT 1 Id, 1 ParentId, ‘Parent A’ Name UNION ALL SELECT 5,1,’Child A1′ UNION ALL SELECT 47894,5,’Child A2′ UNION ALL SELECT 2,2, ‘Parent B’ UNION ALL SELECT 3,2, ‘Child B1’ )TAB ;With CTE as ( select * from… Read More Reverse order of a CTE result?

Advertisements I’m trying to use values inside a cte in snowflake. For example, this: WITH person (id, name) AS (VALUES (1, ‘Ted’), (2, ‘Bill’)) SELECT * FROM person; works fine in (for example) postgres, but gives me the following error in snowflake: SQL compilation error: syntax error line 2 at position 9 unexpected ‘VALUES’. syntax… Read More Using values inside a cte in snowflake queries

Advertisements I’m trying to write a Table-valued function in oracle, which contains a CTE. I’ve been able to do it in SQL Server in this way: ALTER FUNCTION [dbo].[region_parents] ( @regionId INT ) RETURNS TABLE AS RETURN ( WITH cte AS ( SELECT id, owner_region_id FROM regions WHERE id = @regionId UNION ALL SELECT r.id,… Read More CTE inside Table-valued function in Oracle

Advertisements I have the following setup, which seems to be working fine. I am having trouble modifying the query to include the department_name in the output. I can’t seem to get the JOIN working with the CTE. Its probably something trivial but after many attempts I can’t get it to work. Any help would be… Read More Using JOIN with cte

Advertisements I have 2 SQL tables one holding stock information then a style table which details how the stock is used. For some jobs the information is contained with a print file so the value in the stock table would be BIN, if not it would contain a template name which would cause a query… Read More Using SELECT subquery in a CASE statement and handling NULLS

Advertisements I’m working with AWS RDS MySQL and using MySQL Workbench to develop the queries before moving them into the Lambda integration function for my HTTP API on AWS API Gateway. I created this query: use prod_esports; WITH muuid AS (select * from game_match where uuid = ‘2e4f899a-d690-4d41-8c31-c9f89e6a2e4d’), teamID AS (SELECT id FROM team WHERE… Read More Cannot use CTEs for some reason, so how to use subqueries instead, for MySQL RDS on AWS

Advertisements I’m currently writing a query that is grabbing data from a table and I want to update one of the columns in that table. The update needs to be based off the distinct values from one column. For example: Type ID x ID1 x ID1 y ID2 y ID2 z ID3 z ID3 The… Read More SQL Updating Values of Column for Each Unique Value in Separate Column (without a loop)