logo
down
shadow

Deleting rows in a single database table based on the values of other rows


Deleting rows in a single database table based on the values of other rows

By : user2953475
Date : November 21 2020, 01:01 AM
Hope that helps I have a fairly complex requirement I would like to solve using SQL in a Postgres DB. I'm sure this would be addressed in any order management system however I cannot find anything of a similar nature.
code :
Delete from table1
WHERE status in ('FILLED','EXPIRED','REJECTED','CANCELLED')
OR (status in ('PARTIAL','NEW') AND validity not in ('GTD','GTC'))
OR (status = 'REPLACED' and orig_ID not in 
    (select ORig_ID from table1 where status in ('PARTIAL','NEW')));


Share : facebook icon twitter icon
Deleting rows based on multiple values in another table

Deleting rows based on multiple values in another table


By : Bovorn Siriampairat
Date : March 29 2020, 07:55 AM
this will help This statement is returning error 00920- invalid relational operator. , Your brackets are dodgy...
code :
DELETE FROM TABLE15 p
  WHERE (p.item_id, p.product_id) IN
           (SELECT S.item_id, S.product_id
              FROM TABLE14 S
                   );
deleting rows based on conditions in column (retaining rows between two values)

deleting rows based on conditions in column (retaining rows between two values)


By : Karthik
Date : March 29 2020, 07:55 AM
To fix this issue I have a dataframe df1 with two columns: , Try this solution using the tidyverse packages:
code :
library(purrr)
start <- which(c2 == 1) + 1
stop <- which(c2 == 2) - 1
idx <- unlist(map2(start, stop, seq))
df2 <- df1[idx, ]
Deleting rows based on values in other rows

Deleting rows based on values in other rows


By : user2215093
Date : March 29 2020, 07:55 AM
I hope this helps . For more general solution removing only another account_status values per groups if exist at least one active value there:
code :
print (df)
  product  product_id account_status
0  prod-A         100         active
1  prod-A         100      cancelled <- necessary remove
2  prod-A         300         active
3  prod-A         400      cancelled
4  prod-A         500         active
5  prod-A         500         active
6  prod-A         600      cancelled
7  prod-A         600      cancelled

s = df['account_status'].eq('active')
g = df.assign(A=s).groupby(['product','product_id'])['A']
mask = ~g.transform('any') | g.transform('all') | s
df = df[mask]
print (df)
  product  product_id account_status
0  prod-A         100         active
2  prod-A         300         active
3  prod-A         400      cancelled
4  prod-A         500         active
5  prod-A         500         active
6  prod-A         600      cancelled
7  prod-A         600      cancelled
print (df)
  product  product_id account_status
0  prod-A         100         active
1  prod-A         100      cancelled <- necessary remove
2  prod-A         100        pending <- necessary remove
3  prod-A         300         active
4  prod-A         300        pending <- necessary remove
5  prod-A         400      cancelled
6  prod-A         500         active
7  prod-A         500         active
8  prod-A         600        pending
9  prod-A         600      cancelled

s = df['account_status'].eq('active')
g = df.assign(A=s).groupby(['product','product_id'])['A']
mask = ~g.transform('any') | g.transform('all') | s
df = df[mask]
print (df)
  product  product_id account_status
0  prod-A         100         active
3  prod-A         300         active
5  prod-A         400      cancelled
6  prod-A         500         active
7  prod-A         500         active
8  prod-A         600        pending
9  prod-A         600      cancelled
Array Explode values in single database table with 3 different rows print in a table how?

Array Explode values in single database table with 3 different rows print in a table how?


By : Jeremy Nasta
Date : March 29 2020, 07:55 AM
This might help you Your problem is in your for loop, $lenght is the number of categories, but you are looping from 0 to $lenght instead of 0 to $lenght-1 which is resulting in the blank rows. Change your for statement to:
code :
<?php for($i=0; $i < $lenght; $i++)
In SQL, how to query for rows based on the values in other rows at a certain relative position in the table

In SQL, how to query for rows based on the values in other rows at a certain relative position in the table


By : Suresh Rudrakshula
Date : March 29 2020, 07:55 AM
help you fix your problem You can find a record that is the next after some specific time by combining ORDER BY and LIMIT:
Related Posts Related Posts :
  • How to create a database snapshot in SQL Server 2008 R2
  • Query speed and expressions with constant value
  • How to do a complex calculation as this sample
  • Passing a ResultSet into a Postgresql Function
  • Netsuite custom formula field using a CASE statement with multiple WHEN conditions returning "invalid expression&qu
  • Execute query command (dynamic sql) runs faster than ordinary query
  • How to extract only numbered rows from a column
  • INSTEAD OF DELETE trigger (Postgresql)
  • Oracle Apex, when a report with sql is created, how do i get it return after entering in to a text box?
  • Select records based on two tables where one column in one table starts with the records from another tables column usin
  • How to write select using two ids combination in Oracle 11g
  • Frequently use of 'UPDATE STATISTICS WITH FULLSCAN' is normal?
  • need query for this simple pulling data sql server
  • Sql query by using decode to show grades of employee
  • Not a single function error in Oracle
  • Oracle SQL optimizer's behavior when dealing with ORs and row-independent predicates (such as functions returning same v
  • Combining output of two or more select statement
  • Must Declare Scalar Variable Exception
  • Postgres Calculate Difference Using Window Functions
  • SQL Server : unsure how to retrieve selected records
  • SQL unique field: concurrency bugs?
  • Query to get the next identity?
  • SQL Server Adding summing values based on the month
  • Join on month if available else join on latest month
  • How to solve error: ORA-01406 fetched column value was truncated?
  • Selecting Field Multiple Times using OR
  • Which SQL datatype can be used to store mobile numbers in numeric forms, without including characters like brackets and
  • Oracle SQL - "Not a group by expression" after adding CASE statements
  • Runnning total of unique string values in column over days
  • VB.Net to AccessDB by SQL - INSERT INTO with parameters error
  • SQL/Excel/VBA - UPDATE query: 'Syntax Error (missing operator) in query expression'
  • Code to combine two sql queries
  • Setting SQL Date field value based off Week Number and Day Number Field
  • SQL query to find matching records but with differences in case sensitivity?
  • How to sort record inside views in SQL Server 2008
  • How to link the output of teradata sql query to an excel sheet?
  • Matching sets in SQL when cross-referencing
  • error even after following Microsoft format in SQL
  • Increase price by percentage, results rounded
  • Can we Use "Case" in a ColdFusion Query-of-Query
  • displaying 'Active' for a row which has 'A' as value
  • Using pattern search in SQL Server Query
  • SQL - how to group transactions for a certain date pattern?
  • How to Index SQL with multiple AND conditions nested inside OR
  • SQL rank with priority
  • Does BigQuery support SubQueries?
  • Any ideas for persisting H2 Database In-Memory mode transaction?
  • How to improve SQL query performance (correlated subqueries)?
  • Filter repeated columns in Oracle
  • SQL Server join on whichever column has value populated
  • use sql stored procedure to insert data which is returned from a query stored in a table
  • Query not working1
  • SQL - using an alias in a where clause in a subquery
  • How to search for a whole word with special char using oracle contains?
  • Production Perfomance of using SELECT * statements?
  • String concatenation not working as expected in SELECT statement
  • How to detect which stored procedures UPDATE or INSERT into a certain TABLE?
  • Select statement inside CASE - SQL
  • .Net Float error 4 - float(4) = 4.44089209850063E-16
  • How to show as column to recursive rows in a table With SQL Server
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org