[DB-SIG] Positioned Update/Delete

adustman@comstar.net adustman@comstar.net
Thu, 23 Apr 1998 10:10:13 -0400 (EDT)

On Thu, 23 Apr 1998, Dr. Dieter Maurer wrote:

>  > On Thu, 23 Apr 1998, Dr. Dieter Maurer wrote:
>  > > How should positioned update/delete be best used with the DB-API:
> <----- snip ---->
>  > > 
>  > 4a. cursor.execute("update TABLE set CURRENT = ?", (current,))
>  > 
>  > Assuming you are using an SQL-89 or so database; the API doesn't really
>  > specify the query language syntax. 
> I know.
> SQL-92 specifies:
> 	positioned-update::=
> 	  UPDATE [table]
> 	  SET update-assignment-commalist
> 	  WHERE CURRENT OF cursor
> Thus, I could formulate my question better in this way:
>   "How should the cursor (which is a Python object) best be transported
>   into the SQL world."
> I see two interpretations for your suggestion:
>  1. you suggest not to specify it at all (and "current" (in your suggestion)
>     means the new value). But this might conflict with a searched
>     update where the "where" clause has been omitted.

I was assuming what you wanted here was to update the column CURRENT (I
wasn't aware of the use of CURRENT as a reserved word). If there is a is a
where clause, you would need something like this: 

cursor.execute("""UPDATE mytable
                      SET Value1 = ?, Value2 = ?, Value3 = ?
                      WHERE Index = ?""",
               (v1, v2, v3, i))

Values are substituted in the order they appear. You don't need to specify
the cursor in the SQL statement. You are effectively telling the cursor to
execute the SQL statement. 

Andy Dustman                                 WW           Charles Babbage:
ComStar Communications Corp.                 BB       He never used Linux,
(770) 333-8779 | PGP KeyID=0xC72F3F1D        D?        and now, he's dead.