I have an SQL table that looks like this: And I want to add a column that works as an inner ID for each Category such that Category and the new column could work as a composite key. The new column should look like this: How can I write a query to achieve that? Answer You need to give a
Assign group_id to all linked records in many-to-many table
There are 3 tables in my DB; Table 1 has ‘Collateral’, Table 2 has ‘Loans’, Table 3 is a multi-link table between 1 and 2; let’s call it ‘Loan_Collateral_link’. A collateral can belong to 1 or more Loans, and a Loan can have multiple Collaterals. What I want to achiev…
Python SqlAlchemy adding escape characters to json string while inserting in postgresql json column, so queries not working
I am adding JSON string to Postgres jsonb column in python using the sqlalchemy library. Code to create json string is Code to add to postgresql database is: My JSON is printed properly in logs But when it goes to database the json is enclosed in a string and an escape character is added in front of every dou…
Creating a Data Dictionary with example data (SQL)
I am looking to run a script on a SQL database to create a Data Dictionary with an example of the data for each field. To keep it simple I would just like to include the data from the first row of each table along with each table name and column name So something like this: Table Name Field Name
DuckDB – efficiently insert pandas dataframe to table with sequence
Insertion with series works just fine: How I can use the sequence with pandas dataframe? I don’t want to iterate item by item. The goal is to efficiently insert 1000s of items from python to DB. I’m ok to change pandas to something else. Answer Can’t you have nextval(‘serial’) as…
How to get the last/maximum date that is on/earlier than another baseline date by user?
I have a df where I am trying to create the Last Login Date column, as shown in the image. I am not sure how to get the maximum login date that was on/prior the email notification date for that current row. I added explanations on how I expect the data to look. Any help is appreciated in either sql
Convert to string to ‘Proper’ casing
Is there a way to convert a string to ‘Proper’ casing? I’m using the Excel definition of ‘Proper’ which will format text such that the first letter of any word is capitalized and the remaining letters are lower case. Sample Inputs | Outputs I browsed the string function/operators…
Translating pyspark into sql
I’m experiencing an issue with the following function. I’m trying to translate this to a SQL statement so I can have a better idea of exactly what’s happening, so I can more effectively work on my actual issue. I know that this contains a join between valid_data to ri_data, a filter, and a s…
SQL While Loop is Is Printing same ID for Every Record
My resultset has 2958 rows. I’m trying to Loop over the result set and select each row, assigning a new id to it, but each new Id is printing 2958 times. What am I doing wrong and how could I fix this? After the select is correct, I want to use it with the Insert. Answer The main problem in
Update Multiple Values in a SQL query based on row values
Is there a possible way only use SQL Update query to update July Data to August data? E.g. The Value of B in July should be updated to 12. Dimension Key is the primary key and Name is the “durable” key. CURSOR is allowed to use but not preferred. Answer You must join the table to itself to set dif…