[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.