I am having a table with many columns (but posting only col1, col2, col3 here for simplified post): I want to fill null values of source _id with values from id. For example, source_id a5 row has null which has to replaced with id a1 values, subsequently, source_id a6 row having null to be replaced with a5 row Output: Answer
SQL change subquery to join
I have the following query, which has a subquery. I need the subquery converted to a join with a separated-out “where” clause for the dates, which will be parametised in an excel spreasheet. The subquery obtains a count of all the merchant’s transactions between given dates. I’ve tried everything I can think of but I either get too many rows
HIVE SQL: Select rows whose values contain string in a column
I want to select rows whose values contain a string in a column. For example, I want to select all rows whose values contain a string ‘123’ in the column ‘app’. table: result: I am not familiar with SQL query. Could anyone help me? . Thanks in advances. Answer In a number of ways: like: rlike: instr: locate: Invent your
Using PIVOT to get SELECT into one column, not row
I am trying to make a select, which would show the result into ONE column, not row. I tried using PIVOT, but I am not successful at all. Result of my select: I want to have it like this: Answer You can use cross apply to obtain a single column: results: or, if you prefer having a separate column for
Create external table from csv on HDFS , all values come with quotes
I have a csv file on HDFS and I am trying to create an impala table , the situation is it created the table and values with all the ” CREATE external TABLE abc.def ( name STRING, title STRING, last …
How to aggregate json fields when using GROUP BY clause in postgres?
I have the following table structure in my Postgres DB (v12.0) id | pieces | item_id | material_detail —|——–|———|—————– 1 | 10 | 2 | [{“material_id”:1,”pieces”:10},…
Query and Pivot Column for the last last 3 Years SQL
My need is to retrieve sell data from and pivot the last three years and sum the quantities. See example below. I wonder how to do it. I read that SQL pivot is the way to go, but I can’t figure out …
Insert additional text into table fields
I export a CSV-File with shipping weights from my webshop. The problem is there are bulk numbers and no unit Like this: +——+——+ | ID |weight| +——+——+ | 101 | 1,2 | +——+——+ …
How to bulk update sequence ID postgreSQL for all tables
I imported the Postgres SQL file to my server using TablePlus(SQL client), but after I insert new row I got error like this: SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint “users_pkey” DETAIL: Key (id)=(1) already exists I know it caused by sequence value is 0 and need to be updated by code below: But it needs
Date is not displaying correct with substr & like query
I am trying to get this out out, but i am experiencing that the substr i am using is incorrect , For an example , all my columns are displaying hdfs://asdasda/asdas/fdsfdsfd/received_files/asdasd_20191231_11122333_123456789_CO.dat some of which has more character so in order for me to get the exact date in the column is inconsistent if i am using subsring some will return