oracle表更新行,比较oracle表中的行并更新匹配的行

我有一张如下表：**ID tDate Product Price Quantity BuySell Status**

1 10-May-17 pppp $12 20 Buy Null

2 12-May-17 tttt $10 20 Sell Null

3 12-May-17 tttt $10 20 Buy Null

4 18-May-17 pppp $14 20 Sell Null

5 18-May-17 pppp $14 20 Buy Null

6 18-May-17 pppp $14 20 Sell Null

我需要更新名为STATUS的字段，并将其设置为“Matched”，只要找到一对具有相同的tDate、product、price和quantity，而不等于BuySell。在

以下是预期结果：

^{pr2}$

请注意#6如何不匹配，因为它只能与另一个null匹配。在

我希望我可以用一个SQL语句来执行此操作。在

我现在所做的可能是最糟糕的方法：

我用python将其加载到pandas数据帧中，然后遍历每一行进行比较。在s = "SELECT ID, Account, product, Price, tDate, BuySell, Qty" + \

"FROM Table " + \

"WHERE Status IS NULL " + \

"ORDER BY Account, product, tDate, Price, Qty"

df = pd.read_sql(s, conn)

for i in range(len(df.index)-1):

if df.iloc[i, 1] == df.iloc[i+1, 1] \

and df.iloc[i, 2] == df.iloc[i+1, 2] \

and df.iloc[i, 3] == df.iloc[i+1, 3] \

and df.iloc[i, 4] == df.iloc[i+1, 4] \

and df.iloc[i, 5] != df.iloc[i+1, 5] \

and df.iloc[i, 6] == df.iloc[i+1, 6]:

s = "UPDATE Temp_Fees " + \

"SET Strategy = 'UNALLOCATED \ CANCELLED' " + \

"WHERE ID = " + str(df.iloc[i,0]) + \

" OR ID = " + str(df.iloc[i + 1, 0])

#custom function that will execute and commit statement

bb.EXECUTE(s)

#avoid reading a matched row

i = i + 1

谢谢你