Thanks , do you support composed primary keys?I.e multi field , what would be sequel literal symbol syntax, snake case the composed symbol? I want to stay db independent, we will have to see what performance is using straight sequel On Jan 25, 2013 6:07 PM, "Jeremy Evans" <[email protected]> wrote:
> On Friday, January 25, 2013 12:42:51 PM UTC-8, Charles Monteiro wrote: >> >> so here's a complication to my oracle stored proc translation , namely I >> need to update the specific object (row) I'm on. I am now using limit to >> spin thru the dataset so I'm getting rows represented as hashes. I need to >> update that row which that current hash in the iteration represents. >> >> Here's a stored proc snippet that illustrates my woes: >> >> FOR row IN my_cursor >> LOOP >> IF (row.status = 'OP') AND >> ((row.direction = 'I') OR >> (row.status != 'NT' AND row.status != 'TA')) THEN >> IF (row.status != 'RJ') THEN >> UPDATE my_table >> SET status = 'HD', >> status2 = 'Held Prior Days Msg' >> WHERE CURRENT OF my_cursor; >> END IF; >> IF row.seq_nbr > (seq + 1) THEN >> epn_seq := seq + 1; >> UPDATE hdr_msg >> SET seq_nbr = seq >> WHERE CURRENT OF my_cursor; >> UPDATE my_table2 >> SET Hdr_Nbr = epn_seq >> WHERE Link_Name = row.Link_Name >> AND Direction = row.Direction >> AND Hdr_Nbr = row.seq_nbr; >> ELSE >> epn_seq := row.seq_nbr; >> END IF; >> ELSE >> DELETE FROM hdr_msg >> WHERE CURRENT OF my_cursor; >> DELETE FROM dtl_msg >> WHERE Link_Name = row.Link_Name >> AND Direction = row.Direction >> AND Hdr_Nbr = row.seq_nbr; >> END IF; >> END LOOP; >> >> so that beast above has this notion of Oracle's Where Current Of >> >> which is: >> >> Oracle/PLSQL: WHERE CURRENT OF Statement >> >> If you plan on updating or deleting records that have been referenced by >> a Select For >> Update<http://www.techonthenet.com/oracle/cursors/for_update.php> statement, >> you can use the *Where Current Of* statement. >> >> The syntax for the *Where Current Of* statement is either: >> >> UPDATE table_name >> SET set_clause >> WHERE CURRENT OF cursor_name; >> >> OR >> >> DELETE FROM table_name >> WHERE CURRENT OF cursor_name; >> >> The Where Current Of statement allows you to update or delete the record >> that was last fetched by the cursor. >> This seems to be non ansi i.e. oracle, derby, db2 have the construct but >> I'm guessing there's no support for this in sequel. However, since I do >> have to port this what would be the suggested alternative and is there any >> way of ball parking the relative delta in update performance ? >> > > If you are planning on dropping down to the driver level via synchronize, > and using cursors to iterate over your rows, that SQL should work directly. > > If you plan to do this at the Sequel level, the way to do what you want is > to reference the primary key for the row when updating/deleting: > > > DB[:table].where(:primary_key_column=>current_primary_key_value).update(hash_of_new_values) > > DB[:table].where(:primary_key_column=>current_primary_key_value).delete > > No idea what the difference is in update performance, though. > > Thanks, > Jeremy > > -- > You received this message because you are subscribed to the Google Groups > "sequel-talk" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/sequel-talk?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sequel-talk?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
