SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-27-2008, 03:10 AM
| ||||
| ||||
 Re: CURSORs read versus write I'm trying to declare an updateable cursor in an SQL SP using the 'where current of' clause, but because I have to define an 'inner join' in the declare statement it doesn't seem to want to let me. Is there anyway to make a cursor updateable if you are using join statements in the declaration ?      |
|
02-27-2008, 03:10 AM
| |||
| |||
| Re: CURSORs read versus write Giganews wrote: > I'm trying to declare an updateable cursor in an SQL SP using the 'where > current of' clause, but because I have to define an 'inner join' in the > declare statement it doesn't seem to want to let me. Is there anyway to make > a cursor updateable if you are using join statements in the declaration ? > > How many columsn do you introduce from the join. If it's only one column (per joined table) you can use a scalar subquery instead to keep the cursor updateable: E.g. SELECT a, (SELECT b FROM T2 WHERE ..) AS c FROM T1 Cheers Serge -- Serge Rielau DB2 SQL Compiler Development IBM Toronto Lab |
|
02-27-2008, 03:10 AM
| |||
| |||
| Re: CURSORs read versus write I'm selecting three columns in total, 2 from table 1 and a single id column from another with the join on the latter two [id = id] then updating a null value in table 1 with the ID that matches. So in the scalar subquery can I : SELECT a, b , (SELECT b FROM T2 WHERE t2.e = t1.e) AS c FROM T1 t1.b is currently null and is subsequently updated with the value ot t2.b Could I not now however use MERGE to do the same thing leaving the join in the statement ? If I am only dealing with records that match the join and update t1.b with the matched values then I assume that would not require defining 'WHEN NOT MATCHED' Thanks again "Serge Rielau" <srielau@ca.ibm.com> wrote in message news:39oinqF63mcvkU2@individual.net... > Giganews wrote: > > I'm trying to declare an updateable cursor in an SQL SP using the 'where > > current of' clause, but because I have to define an 'inner join' in the > > declare statement it doesn't seem to want to let me. Is there anyway to make > > a cursor updateable if you are using join statements in the declaration ? > > > > > How many columsn do you introduce from the join. > If it's only one column (per joined table) you can use a scalar subquery > instead to keep the cursor updateable: > > E.g. > SELECT a, (SELECT b FROM T2 WHERE ..) AS c FROM T1 > > Cheers > Serge > > -- > Serge Rielau > DB2 SQL Compiler Development > IBM Toronto Lab |
|
02-27-2008, 03:10 AM
| ||||
| ||||
| Re: CURSORs read versus write TD wrote: > I'm selecting three columns in total, 2 from table 1 and a single id column > from another with the join on the latter two [id = id] then updating a null > value in table 1 with the ID that matches. > > So in the scalar subquery can I : > > SELECT a, b , (SELECT b FROM T2 WHERE t2.e = t1.e) AS c FROM T1 > > t1.b is currently null and is subsequently updated with the value ot t2.b > > Could I not now however use MERGE to do the same thing leaving the join in > the statement ? If I am only dealing with records that match the join and > update t1.b with the matched values then I assume that would not require > defining 'WHEN NOT MATCHED' > > Thanks again > > > "Serge Rielau" <srielau@ca.ibm.com> wrote in message > news:39oinqF63mcvkU2@individual.net... > >>Giganews wrote: >> >>>I'm trying to declare an updateable cursor in an SQL SP using the 'where >>>current of' clause, but because I have to define an 'inner join' in the >>>declare statement it doesn't seem to want to let me. Is there anyway to > > make > >>>a cursor updateable if you are using join statements in the declaration > > ? > >>> >>How many columsn do you introduce from the join. >>If it's only one column (per joined table) you can use a scalar subquery >>instead to keep the cursor updateable: >> >>E.g. >>SELECT a, (SELECT b FROM T2 WHERE ..) AS c FROM T1 >> >>Cheers >>Serge >> >>-- >>Serge Rielau >>DB2 SQL Compiler Development >>IBM Toronto Lab > > > *lol*Today shall be hencforth kown as merge-day. Yes you can use MERGE. But I thought you wanted to use a cursor.... Cheers Serge -- Serge Rielau DB2 SQL Compiler Development IBM Toronto Lab |
Linear Mode
