In MySQL, I have INSERT … SELECT ON DUPLICATE KEY UPDATE query as below: UPDATE: This is the result from above query. Now I want to create an array of the user_id of either the insert or the update the records. So, my expecting array should be I tried it something like this, but it doesn’t work fo…
Tag: sql
Postgres: Update jsonb from other table column which is encrypted
I am migrating a data from an encrypted jsonb column (app_id field) in a table to another jsonb column in a different table. As said: origin table column is encrypted. Destination one is not. Right now I have the following query: Error response is: SQL Error [42804]: ERROR: could not determine polymorphic typ…
MySql format number with space as thousand delimiter and no decimal zeros
Having the number: 103,648.340 format it to be as the expected result Expect result: 103 648.34 My progress: TRIM remove decimal zeros, so I’ve 103,648.34 FORMAT add thousand delimited by space, so I’ve 103 648.340 I’ve tried to combine TRIM with FORMAT without success Answer Try this soluti…
SQL – trimming values before bracket
I have a column of values where some values contain brackets with text which I would like to remove. This is an example of what I have and what I want: I have only come across approaches that use the number of characters or the position to trim the string, but these values have varying lengths. One way I was
Strange behavior from lag(), skipping over certain rows
I have a table we’ll call service, toy version is: The last five system fields are for database bookkeeping, and to enable soft (front end) deletion. There’s another table service_log which, together with service allows us to view a full revision history for each record in service. Fields of servi…
SQL Count(*) and Query a UNION query
I am a novice to SQL and seeking a little direction. The below query correctly identifies the number of rows (count(*)) in the combined (Union) query. However, in addition to the number (count), I need to also display the Name (cn1.FullName) as there are multiple users. I have looked high and low for a simila…
Rails: How to use instance method in Active Record Query
I am having a filter query which should return all the records where either attribute (column) “status” is not “done”, or instance method “completeness_status” does not return “done” The query is something like that: Studies.where(“studies.status != ? OR s…
Query for all roles with a given privilege grant in Snowflake
Is there any means of querying Snowflake for all the roles (and/or users) where a specific privilege has been granted? I know how to show grants to <role> but I’m not so sure of how to find the roles that have a grant. This is not something that needs to happen regularly–i’m just looki…
Compare two columns in SQL
I’m new to SQL and have very basic queries in GCP. Let’s consider this table below: Name B C Arun 1234-5678 1234 Tara 6789 – 7654 6789 Arun 4567 4324 Here, I want to compare column B and C and if they match then give 1 else 0 in column same and else different (which we have to create). So
Update query with join and where clause
I’m currently confused with a (fairly easy) update statement that I’m trying to execute on a table. The two tables are as such: Customer table has the columns Loan table has the columns I would like to update the passworddisabled attribute to true if they are registered via a specific cash registe…