Questions tagged [syntax]

The set of rules that define the combinations of symbols that are considered to be correctly structured for that language.

Filter by
Sorted by
Tagged with
3 votes
2 answers
355 views

Can I write a FULL OUTER JOIN without OR IS NULL?

Here's some data to play with: CREATE TABLE a ( a_id int NOT NULL, a_prop text NOT NULL ); CREATE TABLE b ( b_id int NOT NULL, b_prop text NOT NULL ); INSERT INTO a VALUES (1, '...
user avatar
  • 415
0 votes
0 answers
18 views

MySQL Storage Procedures: Automatic Hourly Partition Maintenance Syntax Issue

So I am streaming data from MySQL (fast in writes) to ClickHouse (insanely fast in reads). My MySQL to ClickHouse service copies the MySQL rows to ClickHouse compressed columns quite fast, so I do not ...
user avatar
0 votes
3 answers
102 views

MySQL dump import into MariaDB syntax error to use near '*/'

This is the result from mysqldump 10.13 and MySQL 8.0.28 Server. /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` int(11) DEFAULT ...
user avatar
  • 121
0 votes
0 answers
61 views

Syntax error in different version of Postgres

Following statement insert into abc.ad_campaign as ac (client_id, acquisio_id, name) values (356619, 5356522, 'Massage - Hand & Stone') ON CONFLICT(client_id, acquisio_id) DO UPDATE SET name = ...
user avatar
1 vote
1 answer
54 views

Trigger uses a procedure or a function?

PostgreSQL version 10 and before use the term "Trigger Procedure" (https://www.postgresql.org/docs/10/plpgsql-trigger.html). But later versions use the term "Trigger Function". ...
user avatar
1 vote
0 answers
17 views

Postgresql -- join syntax 'bug' that actually ran [duplicate]

I noticed I wrote a query like this, the day after I wrote it: SELECT T1.col1, T2.col1, T3.col1 FROM our_schema.table1 T1 INNER JOIN our_schema.table2 T2 LEFT JOIN our_schema.table3 T3 ON (T2....
user avatar
1 vote
0 answers
67 views

Scalar function parses and runs in SSMS, incorrect syntax in ScriptDOM

I'm preparing the Database migration from compatibility level 140 (SQL Server 2017) to level 150 (SQL Server 2019). As part of the preparation, Data Migration Assistant (DMA) has been run and it found ...
user avatar
  • 1,060
0 votes
1 answer
44 views

Query to find data where value of data changed to 0

I am trying to write a query to find the rows where value of particular column changes to 0 from some other value say 1 or above. The value may change from 0 to other value again. But we need data for ...
user avatar
0 votes
1 answer
25 views

Mongo DB Query does not give desired result

I have two collections in mongo db. One called list with keys id and movieid in it. One called movies with movieid and Genre in it. The results I am trying to retrieve are: Which listid has the ...
user avatar
  • 1
1 vote
1 answer
73 views

MySQL Row Constructor forms in INSERT VALUES vs. VALUES

In MySQL 8, based on my reading of the documentation, INSERT VALUES accepts row constructors in the form: INSERT ... VALUES(1,2,3), (4,5,6), (7,8,9); or INSERT ... VALUES ROW(1,2,3), ROW(4,5,6), ROW(...
user avatar
0 votes
1 answer
2k views

ERROR: syntax error at or near "END" [closed]

I am trying to play around with trigger and found some samples online which I pretty much copied before except changed table name and row counts. But somehow I kept on getting syntax error and ...
user avatar
  • 115
0 votes
1 answer
838 views

dbeaver check sql for syntax without running

Using the dbeaver tool, I want to check a script for syntax errors without actually running it. Purpose: For a long insert query (postgresql syntax, PostgreSQL 10.15) in the form of: INSERT INTO ...
user avatar
-1 votes
1 answer
32 views

how do I filter my data from one column based on another?

I need to filter the sum(f.film_id) as films made to be based on the actor who made them or the actor_id. however I'm having a hard time figuring out the syntax for said query. select concat(a....
user avatar
0 votes
1 answer
23 views

Migration db (error when alter DB)

i am new as DBA. i want to migrate db, can i do one time alter for all data ?. or must one alter to one data ? this my query. DECLARE @name VARCHAR(50) DECLARE db_cursor CURSOR FOR SELECT name FROM ...
user avatar
0 votes
1 answer
1k views

An expression of non-boolean type specified in a context where a condition is expected, near 'AND' [closed]

ALTER PROCEDURE [dbo].[usp_BaseScan_CycleCount_Report] @StartDate varchar(10), @EndDate varchar(10) AS BEGIN IF @StartDate AND @EndDate IS NOT NULL SELECT * FROM dbo....
user avatar
1 vote
1 answer
260 views

Using parameters in stored procedure with plpgsql

I am trying to return the count of overlapping polygons. The problem is that it complains about my "$1" signs: Error is "SQL state: 42601", syntax error at "$1". Why ...
user avatar
-1 votes
2 answers
57 views

How to get a column from a table that corresponds to another table [closed]

I have these tables: OrderDetails: Orders: Employees: Products: Customers: I want to write a query where I can get the last name of the employee with the largest order quantity. I managed to ...
user avatar
  • 99
0 votes
1 answer
40 views

Postgresql script doesn't work as I expect

DO $$ DECLARE companyNrs text[] = ARRAY(SELECT LPAD(companynr::text, 5, '0') FROM s_users); companyNr text; BEGIN FOREACH companyNr IN ARRAY companyNrs LOOP raise notice '...
user avatar
0 votes
1 answer
85 views

Error running PostgreSQL query from SQL Server 2016

The below query runs well in SQL Server 2016: select ResellerId, vCompanyName ,x.* from wlt_tblReseller AS main outer apply ( select (count (ipkReportTypeId)) AS "count", vReportTypeName ...
user avatar
  • 31
0 votes
1 answer
473 views

Replace all column values with other values in the same column according to primary key

I'm querying the following table: id value applicable id1 5 Unknown id1 5 Unknown id1 6 Yes id 2 5 Unknown id 2 5 No id 2 3 Unknown I would like to replace all "Unknowns" in the ...
user avatar
1 vote
1 answer
36 views

Is it possible to reduce the duplication in this simple query?

I'm new to SQL and so this is probably a stupid question but can I reduce the duplication in this query? My actual query involves more complex expressions. Right now I'm just doing string ...
user avatar
-1 votes
1 answer
30 views

What does "player != ANY (SELECT player FROM Member)" return?

I have this table and these queries I've run both queries with these results: I'm confused about the line with the WHERE clause in each query. Specifically that ALL and that ANY. I understand what ...
user avatar
0 votes
1 answer
12 views

Counting number of occurrences, how to "group by"

I have the following query, which works as intended. Database is Sybase SQLAnywhere, which follows t-sql syntax for the most part. I'm trying to add a column to COUNT the number of times each "...
user avatar
  • 153
0 votes
1 answer
79 views

Postgres SQL: INSERT from DELETE

I have a table with id,user_id,timestamp and I'm trying to INSERT into the table, the user_id from a DELETE from table where id=some_number and timestamp. So what I'm trying to achieve looks like: ...
user avatar
2 votes
1 answer
300 views

Inserting date range arrays daterange[]

I'm trying to insert the value of an attribute of a tuple, which would be an array of date ranges, i.e. a daterange[], in a previous tuple accepted the value: {"[2013-05-08,2018-03-22)"} ...
user avatar
6 votes
1 answer
2k views

Syntax shorthand for updating only changed rows in UPSERT

I have a query which upserts into Postgres when a key conflicts only if there are changes to one of the other columns (to avoid unnecessary inserts and returned rows where nothing was actually changed)...
user avatar
  • 163
-2 votes
2 answers
2k views

MySQL Join two tables with comma separated values

I've got two tables as below: CARLINE option_value option_name tipologia_auto_1 Citycar tipologia_auto_2 Berlina tipologia_auto_3 SUV Hobby_1 football Hobby_2 Basket Datimarketing (is the ...
user avatar
2 votes
1 answer
105 views

how to use the keyword `VALUES` in an `IN` statement?

I am on sql server 2016 select @@version Microsoft SQL Server 2016 (SP2-CU15) (KB4577775) - 13.0.5850.14 (X64) Sep 17 2020 22:12:45 Copyright (c) Microsoft Corporation Enterprise Edition: Core-...
user avatar
0 votes
1 answer
97 views

PSQL cte delete seem have have ambiguity when select same column from delete table

Both these two sql can be execute , the first one delete all , the second one is logic right. the first one with d0 as ( delete from seller_create_request where seller_id in (...
user avatar
  • 139
-3 votes
1 answer
3k views

What is "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4" for? [closed]

I'm exploring Sakila database. After CREATE TABLE, I saw this syntax. Could any one help to let me know what is this and what it is for? how and when to use it? (I didn't see this in my SQL course) ...
user avatar
2 votes
1 answer
145 views

Why is the ROW keyword needed when constructing a row with one element, but not two?

In PostgreSQL 11, the following expression UPDATE test_table SET ("column_a","column_b") = ('value-a','value-b') WHERE "column_a" = 'value-c' is accepted and performs ...
user avatar
0 votes
1 answer
262 views

"ENUM" is not vallid at his position, expecting ')' [closed]

I'm keeping this error and I'm very new to mySQL so I don't know what is going on. The only way I found to remove this error message is to put ')' before the ENUM and then the message change - "...
user avatar
  • 1
0 votes
1 answer
22 views

How do I get rid of the duplicate code in this query?

I have this query, which works: SELECT *, ("last done" + "auto time span"::interval) AS "new last done" FROM personal."time tabs" WHERE "reset ...
user avatar
6 votes
2 answers
7k views

Why is "INSERT INTO table;" a syntax error?

I have this: CREATE TABLE people ( id bigserial, timestamp timestamptz DEFAULT now() NOT NULL, PRIMARY KEY (id) ); CREATE TABLE "...
user avatar
0 votes
1 answer
692 views

Getting generic Oracle error message 'missing right parenthesis'

CREATE TABLE IDN_ORG ( ID VARCHAR2(255) NOT NULL, TENANT_ID INTEGER NOT NULL, NAME VARCHAR2(255) NOT NULL, CREATED_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, LAST_MODIFIED TIMESTAMP ...
user avatar
0 votes
1 answer
35 views

Why are CASE-expressions in the list documenting operator precedence?

In the list here: https://mariadb.com/kb/en/operator-precedence/ case-expressions are put at the same precedence level as BETWEEN, between the NOT operator and the comparison operators. However, case-...
user avatar
  • 279
0 votes
2 answers
33 views

SQL CASE query prioritization

I'm doing a pokemon sql table and created a new column named capacity_difference to determine the collectability of each pokemon in the table. However, when I want to use CASE query to categorize them,...
user avatar
  • 11
2 votes
1 answer
176 views

Function accepts only certain passwords in postgreSQL

I have created a function in a postgreSQL(v10.10) database to create new users or update existing ones. As parameters you pass the username, password, role memberships, first name and last name. In my ...
user avatar
  • 123
1 vote
0 answers
16 views

Why did Codd, et al. prohibit intermediate scalars in SELECT - and how would they have recommended avoiding syntactic repetition?

(I'm asking this because I was reviewing this older question I asked and because I'm currently writing some queries and getting annoyed at how much I need to repeat myself). In ISO-compliant SQL ...
user avatar
  • 400
1 vote
1 answer
1k views

Are there better alternatives rather than using a "Full outer join" when it is not possible to use a "left or right join"?

I have two tables with below structure : Create table Modern_transaction (Eeffective_date Date, Customer_Num Number, Branch_Code Number, MTrns_type_one Number, ...
user avatar
  • 1,294
0 votes
1 answer
23 views

Query with "Logical operator (AND)" that does not return the desired result set

I have a table: create table test_table (customer_num NUMBER, dep_group VARCHAR2(50), dep_type VARCHAR2(50), balance NUMBER) the table has two records: " ...
user avatar
  • 1,294
0 votes
1 answer
382 views

Error "ORA-0091:Invalid Character" when executing a PL-SQL statement in Oracle [closed]

I'm new to oracle pl-sql and I'm facing error ORA-0091:Invalid character when executing this query below. But when I comment the Execute Immediate statement and use dbms_output.put_line the result of ...
user avatar
  • 1,294
0 votes
0 answers
40 views

MySQL - Year Over Year - Query Syntax

Experts I am working on a query to get Year over Year data comparison from current year to prior year. For example, if current year is Jan 1st - now, I would calc that dynamically in my query but ...
user avatar
-1 votes
1 answer
189 views

Returning only 1 row that meets some criteria [duplicate]

This is an issue that I run into quite frequently, and which I would love to know how to work through. I'm writing a report/query that is simple enough, but when run, returns rows with duplicate ...
user avatar
  • 153
0 votes
1 answer
31 views

Can't add or update child, Foreign Key Error

I'm kinder new in MySQL. I was trying to insert value in a relational database. I know that i have to insert in parent table before doing it in child table. Assume we have tables named STATE, COUNTRY....
user avatar
1 vote
1 answer
24 views

Move column to another table by reducing?

I'm not very experienced with database so sorry for lacking correct terminology. I have a structure similar to Employee -Id -Location -Name -StoreId Store -Id -Name And I want to simplify/...
user avatar
-1 votes
1 answer
482 views

SUM first consecutive rows only SQL Server

I have the following data: ROW SEGMENT ORDEN LONG 1 39 11 49.00 2 39 10 102.00 5 39 7 104.00 6 39 6 139.00 7 39 5 97.00 11 39 1 ...
user avatar
2 votes
1 answer
2k views

SQL query with "INTO OUTFILE" does not work on MariaDB 10.4 but does on 10.0

I have an SQL query that executes perfectly on MariaDB 10.0.35 but fails with a syntax error on MariaDB 10.4.12. I attempted to contact them, but they told me to check forums and compare the ...
user avatar
0 votes
2 answers
34 views

How do I write a query that inserts values while AND gets values from another table?

I have a table (Tbl2) that needs to be populated with values from another table (Tbl1). Tbl1 has columns: id (uuid), name, address, gender. Tbl2 has columns: id (uuid), org, name, address, gender, ...
user avatar
2 votes
1 answer
27 views

Weird PostgreSQL expression parsing

How is PostgreSQL parsing supposed to work? rivet=# SELECT 1 IS NOT NULL::int + 2 IS NOT NULL::int; int4 ------ 1 (1 row) But rivet=# SELECT 1 IS NOT NULL::int + (2 IS NOT NULL)::int; ?...
user avatar

1
2 3 4 5