MS SQL Server :: One Statement Update

Update Statement With Join
Hey guys,
Up to this point I've been dealing with mostly select statements but time has come, and I need to do an update. Basically I'm not sure how to structure the query.

I'd like to update the field "new_applicationreceived" to the value of "new_lastcontact" based off the results of the following select query:

select new_lastcontact from lead
LEFT JOIN StringMap ON Lead.SalesStageCode = StringMap.AttributeValue
AND StringMap.AttributeName = 'SalesStageCode'
AND StringMap.ObjectTypeCode = 4
where new_applicationreceived is null
and lead.salesstagecode = 5

I'd really appreciate your help - I'm very worried about messing up the data and don't want to screw something up.

View Replies !

Can We Have An Inner Join Clause In An Update Statement
Hi,
I'm trying to inner join an update statement.
Something like this:

update #point_connection_temp AS a inner join #point_connection_temp_two as b on a.key_fld = b.key_fld set a.next_point = b.next_point
where #point_connection_temp.next_point is null
order by a.key_fld

I'm getting an error message:Incorrect syntax error near AS
Any help will be greatly appreciated.Thank you!!!!!!!!!1

View Replies !

UPDATE Statement With A JOIN Condition
Hi all,

HERE IS MY UPDATE STATEMENT:

Update WACTS_Inventory_Part_Loc_Tbl SET WIPLT_Part_New_Qty = WIPLT.WIPLT_Part_New_Qty + tmp.MATIN_QTY FROM

WACTS_Inventory_Part_Loc_Tbl WIPLT

INNER JOIN

Temp_MatIn_Data tmp

ON

WIPLT.WIPLT_Part_Number_MSBA=tmp.PARTS_ShortID

WHERE

WIPLT.WIPLT_Location_Code='MF'

I have a Problem with this satment becoz my Temp_Matin_Data_Tmp table contains two records with the same PartNumber that is (ShortId) and the two records have two different quantities.

The WACTS_Inventory_Part_Loc_Tbl has only one Part NUmber Record and the key is the PartNUmber and Location_Code.

Now when the Update Satement runs it updates the qty only from the first record of the temp table and does not update the quantity of the second record from tne source table.

I woould appreciate any help on this.

Thanks,

Jothi

View Replies !

Join Cursor With Table Outside Of Cursor
part 1

Declare @SQLCMD varchar(5000)
DECLARE @DBNAME VARCHAR (5000)

DECLARE DBCur CURSOR FOR
SELECT U_OB_DB FROM [@OB_TB04_COMPDATA]

OPEN DBCur
FETCH NEXT FROM DBCur INTO @DBNAME

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' +
+ 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' +
+ 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL'
EXEC(@SQLCMD)
PRINT @SQLCMD
FETCH NEXT FROM DBCur INTO @DBNAME

END

CLOSE DBCur
DEALLOCATE DBCur

Part 2

SELECT
T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE,
SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE,
SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT
INTO #MAIN
FROM
ORDR T0
JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY
LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE
LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY
LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE
WHERE ISNULL(T0.U_OB_TID,0) <> 0
GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS

my question is,
how to join the part 1 n part 2?
is there posibility?

View Replies !

Please Help Obi-Wan: Efficient Join/Cursor/Something/Anything?
Hi allI have a bit of a dilema that I am hoping some of you smart dudesmight be able to help me with.1. I have a table with about 50 million records in it and quite a fewcolumns. [Table A]2. I have another table with just over 300 records in it and a singlecolumn (besides the id). [Table B]3. I want to:Select all of those records from Table A where [table A].descriptiondoes NOT contain any of (select color from [table B])4. An exampleTable Aid ... [other columns] ... description1the green hornet2a red ball3a green dog4the yellow submarine5the pink pantherTable Bidcolor55blue56gold57green58purple59pink60whiteSo I want to select all those rows in Table A where none of the wordsfrom Table B.color appear in the description field in Table A.I.E: The query would return the following from Table A:2a red ball4the yellow submarineThe real life problem has more variables and is a little morecomplicated than this but this should suffice to give me the rightidea.Due to the number of rows involved I need this to be relevantlyefficient. Can someone suggest the most efficient way to proceed.PS. Please excuse my ignorance.CheersSean

View Replies !

Need Help With A SQL Statement - Trying Not To Use A Cursor
I'm just know basic SQL but not enough to write any complex queries.The problem I'm facing right now keeps me thinking to use a Cursor butI've seen a lot of posts on here saying Cursors are bad so I'm hopingthere is a complex query that can give me the data I need.I have about 6 pages in website where I need to display a datagrid ofinformation. There should be 5 columns, Filename, and then 4 CategoryTitles (These category titles are stored in a table calledPageCategory). I have another table, XREF_Doc_Page that stores thePageID, DocID (ID to know what file it is), and PageCategoryID. So Ican query this table with a pageID to see all the results that shouldbe on this page but I don't know how to format it the way I need mydatagrid?In order to have the records from PageCategory be columns, is this acrosstab query or something?My only thoughts right now are to user a cursor to query Pagecategoryand build a temp table somehow with these as the columns?? (Not surehow'd that would work yet).So the datagrid would have the 5 columns like I said and then justlist all files associated with this page and put a checkmark underwhichever category it was assigned to (example below...)Files PageCat1 PageCat2PageCat3 PageCat4abc.pdf Xxyz.pdf Xjkl.pdfx

View Replies !

Using Select Statement Instead Of Cursor
Hi All, Can anyone please help?

TableA has data as below:

ssn sex dob rel_code
111111111 m 19500403 m
111111111 f 19570908 w
111111111 f 19770804 d
111111111 f 19801203 d
111111111 f 19869712 d
111111111 m 19870907 s
111111111 m 19901211 s

I have to convert the rel_code into a specific manner so the data will look as below in TableB:

ssn sex dob rel_code
111111111 m 19500403 01
111111111 f 19570908 02
111111111 f 19770804 20
111111111 f 19801203 21 111111111 f 19869712 22
111111111 m 19870907 30
111111111 m 19901211 31

Member's rel_code = 01
spouse's rel_code = 02
daughter's rel_code starts from 20 with the oldest and increments by 1.
Son's rel_code starts from 30 and increments by 1 from oldest to the youngest.

I know You can write a Sp with cursor and do this, but I would like to know if you can accomplish the same thing by a select or case or something else instead of a cursor.

Thanks in advance.

Jannat.

View Replies !

Select Statement In Cursor
Hi...

I have a stored procedure that rertrieves data from an sql database
and sends out a mail to each receipient who meets the criteria

I am using SQL mail.

I dynamically generate the where clause for my sql query based on criteria taken
from other stored procedures and store it in a varchar variable
called @sqlquery

When i have the following code to run my cursor

DECLARE overdue3 CURSOR
LOCAL FORWARD_ONLY
FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City,
Events.E_ID FROM Events, IndustryEvents + @sqlquery2
OPEN overdue3

I get an error message at the '+' sign
which says, cannot use empty object or column names, use a single
space if necessary.

What should i do. i have tested the variable @sqlquery and it is
definately not blank. There is no bracket error or anything.

Please help!!!

Thanks much indeed

Ramesh

View Replies !

Select Statement In Cursor...Please Help
Sorry to disturb you guys but I have a problem
on the select statement in sql cursor

My select statement is stored in 2 variables one holds the select clause
and the other holds the where clause

I am doing a small test as my seelct statement is very complicated
lots of joins and it is built up from lots of parameters
from other queries and from another stored procedure as well

Hope you can help

when i type the following code:

declare @query varchar(100)
declare @query2 varchar(100)

set @query = "SELECT FROM ml_testMaillist "
set @query2 = " WHERE m_Email= 'ramesh@go-events.com' "

DECLARE overdue2 CURSOR

LOCAL FORWARD_ONLY
exec(@query + @query2)

open overdue2

I get the error

Server: Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'exec'.

Please please help as this is very impt to me
Thanks Thanks

Regards

View Replies !

How To Capture The Value For A CURSOR Statement
Hi everyone,

The following snippet of code returns something like that: string;string1;string2

Up to here fine. I woner how to export such value to ssis variable??? That variable will contain the value needed for the FILEATTACHMENTS property (Send Mail Task)

Thanks a lot,

declare @anex as varchar(500)
declare @anex2 as varchar(700)
set @anex2 = ''
DECLARE anexos CURSOR FOR
SELECT [Ruta] + [Fichero] as ANEXO
FROM SVC_FICHEROS INNER JOIN SVC_ENVIOS ON SVC_FICHEROS.IDENVIO = SVC_ENVIOS.IDENVIO
WHERE ENVIADO = 0
OPEN anexos;
FETCH NEXT FROM anexos INTO @anex

WHILE @@FETCH_STATUS = 0
BEGIN
IF @anex2 = ''
begin
set @anex2 = @anex
end
else
begin
set @anex2 = @anex2 + ';' + @anex
end
FETCH NEXT FROM anexos INTO @anex
END
CLOSE anexos
DEALLOCATE anexos

View Replies !

Help With Cursor And Fetch Statement
Hello,

I am hoping someone can help me with using the cursor and fetch functions. I have not used these features in the past and I am now stuck when trying to use IF statements with the fetch function.

I have a temp table populated with the below headers and their associated data.

The headers are as follows:
ItemRcvdKey, TranID, TranDate, QtyReceived, UnitCost, ItemKey, WhseKey, ItemID, ShortDesc, WhseID, QtyOnHand, StdCost.

The information contained in this temp table lists every single receipt of goods against all of our inventoried items. The QtyOnHand listed on each record is the total QtyOnHand for that item in that warehouse. What I need the fetch to do is grab the receipt of goods records, starting with the most recent TranDate, and pull them into the new temp table until the QtyOnHand is reached. The QtyonHand it should be comparing too is the one listed on the first fetched record. Once the Sum of the QtyRcvd is equal to or is greater than the QtyOnHand for that item I need the fetch to move on to the next item number and perform the same function.

One thing I need to be clear on is that if there are 3 Receipt Records(TranID) for Item A in Warehouse A, the total QtyOnHand will be listed 3 times. I need to make sure that the Fetch is comparing all the records for Item A in Warehouse A to one instance of the QtyOnHand.

The other aspect is that there will be receipt of goods for the same item in multiple warehouses. So I also need the Fetch to be sure that when it is grabbing records and putting them in the temp table, it makes sure it is matching the ItemID and the WhseID with the record it started with.

The current script I have written is below. If you can offer any help I would greatly appreciate it.

Code SnippetDeclare @ItemID VarChar(30),
        @QtyOnHand Decimal (16,8),
        @WhseID VarChar (6),
        @SumRcvd Int,
        @TranID VarChar(30),
        @TranDate DateTime,
        @QtyRcvd Decimal (16,8),
        @UnitCost Decimal (16,8),
        @ItemKey Int,
        @WhseKey Int,
        @ShortDesc VarChar (40),
        @StdCost Decimal (16,8)

        DECLARE Temp_cursor CURSOR FOR
            SELECT TranID, TranDate, QtyRcvd,
                UnitCost, ItemKey, WHseKey,
                ItemID, ShortDesc, WhseID,
                QtyOnHand, StdCost
            FROM #Temp1 tem

    OPEN Temp_cursor

        FETCH NEXT FROM Temp_cursor
            INTO @TranID, @TranDate, @QtyRcvd,
            @UnitCost, @ItemKey, @WHseKey,
            @ItemID, @ShortDesc, @WhseID,
            @QtyOnHand, @StdCost


    WHILE  @@FETCH_STATUS = 0

    BEGIN -- 0

        Insert Into #Temp3
        (TranID, TranDate, QtyRcvd,
        UnitCost, ItemKey, WHseKey,
        ItemID, ShortDesc, WhseID,
        QtyOnHand, StdCost)

        Values
        (@TranID, @TranDate, @QtyRcvd,
        @UnitCost, @ItemKey, @WHseKey,
        @ItemID, @ShortDesc, @WhseID,
        @QtyOnHand, @StdCost)

    FETCH NEXT FROM Temp_cursor
    INTO @TranID, @TranDate, @QtyRcvd,
        @UnitCost, @ItemKey, @WHseKey,
        @ItemID, @ShortDesc, @WhseID,
        @QtyOnHand, @StdCost

View Replies !

Multiple Tables Used In Select Statement Makes My Update Statement Not Work?
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian

View Replies !

Parameter In Declare Cursor Statement
I have to specifiy the database name which is supplied from the user (@fixdb). I want to do something like the following 'code'

Declare SysCursor cursor for + 'select Name, ID from ' + @fixdb +'.dbo.sysobjects where xtype = "u"'

but I can't seem to come up with the right statement.

Any help greatly appreciated.

Thanks,
Judith

View Replies !

How To Specify Which Database To Use For A Select Statement Within A Cursor?
Hi everyone,

I have been trying to perform the following task:

Using the sys.databases & sys.sysindexes views to display all the columns with a clustered index for all tables and all databases in a given server. So the end result will have 3 columns:

Database name
Table name
Column name from that table with a clustered index

I have already created the following script which displays all the databases for a given server:

declare @DBname nvarchar(128)
declare testCursorForDB cursor
for
select name from sys.databases with (nolock)
where name not in ('master','tempdb','model','msdb')
order by name

open testCursorForDB
fetch next from testCursorForDB
into @DBname

while @@fetch_status = 0
begin
    print @DBname
    fetch next from testCursorForDB
    into @DBname
end

close testCursorForDB
deallocate testCursorForDB

I also have created the following query which will display all the table and column names which have a clustered index for a given database:

select object_name(i.id) as TableName,

i.name as IndexName
from sys.sysindexes as i with (nolock)
where i.indid = '1'

However, what I need help/advice on is how do I combine these two together into one working script (either using nested cursors or a better way). In other words, how can I specify which database to use (ie. using the "use database_name") so that my query above will be applied to each database found within the cursor.

Any help is greatly appreciated

Thanks!

View Replies !

UPdate Cursor
i' trying to get the date as yyyymmdd but i somehow seem to be only getting 20062 7 as in 7th Feb 2006. i wrote an update cursor to do this but not sure if this is the best way to go about doing this

declare @day as char(2)
declare @month as char(2)
declare @year as char(4)
declare @Exportdate as char(10)
declare @isodate as datetime

DECLARE IsoDate CURSOR DYNAMIC FOR

SELECT modifieddate
FROM statenet.dbo.tblCAABimport
WHERE modifieddate <> ''
FOR UPDATE

Open Isodate
FETCH NEXT FROM isodate into @isodate
WHILE (@@FETCH_STATUS = 0)
BEGIN

if len(datepart(d,@isodate)) = '1'
set @day = ('0' + datepart(d,@isodate))
else
set @day = ('0' + datepart(d,@isodate))
print @day

if len(datepart(m,@isodate)) = '1'
set @month = '0' + datepart(m,@isodate)
else
set @month = '0' + datepart(m,@isodate)
print @month

set @year = datepart(yyyy,@isodate)
set @exportdate = @year + @month + @day
print @exportdate

update tblCAABimport
set isodate = @exportdate
Where current of isodate

FETCH NEXT FROM isodate
INTO @isodate
END

CLOSE isodate
DEALLOCATE isodate

View Replies !

Update Cursor
I am trying to use a cursor to insert data into another table. It is not working. Below is the cursor and the error message. Can anyone tell me what I am doing wrong. Thank you. Booker

declare cursor_test CURSOR for
select emp_ssn, effective_date1 from temp_employee_benefit_load
open cursor_test
declare @ssn char(9), @process_date char(8)
fetch next from cursor_test into @ssn, @process_date
while (@@fetch_status=0)
update test_cursor
set ssn = @ssn, process_date = @process_date
where current of cursor_test
fetch next from cursor_test into @ssn, @process_date

close cursor_test
deallocate cursor_test

Error Messages...
Server: Msg 16933, Level 16, State 1, Line 0
The cursor does not include the table being modified.
The statement has been terminated.
Server: Msg 16933, Level 16, State 1, Line 5 (this message repeats)...
The cursor does not include the table being modified.
The statement has been terminated.

View Replies !

Update Cursor
hi,

can anybody give me an example of an update cursor. With wich i mean 'cursor for update ...'.

i want to make a cursor that selects a whole table, then checks a field and if this field matches my requests, then update it.

I have no experience with cursors.

Thanx in advance.

View Replies !

Cursor For Update
can some one give me an exampe how to use
CURSOR FOR update?

View Replies !

Where Statement Compared With Join Statement
for complex views should I use "where" statements or "joins" in terms of performance?

Which one is faster?

View Replies !

[SQL Server 2000] How Can I Create Cursor For A SQL Statement?
I have a SQL statement stored in a SQL varriable (after a lot of conditions)

Code:

declare @sql char(100)
set @sql = 'select ma_kh, ten from _khang'

Now, I want to create a cursor to recalculate some values
I've tried:

Code:

declare cur_T cursor for exec(@sql) open cur_T

but it doesn't work.
Can I have another way to do that???

View Replies !

How To Put Condition In Select Statement To Write A Cursor
col1          col2 col3   col4
36930.60   145    N   . 00
17618.43   190   N    . 00
6259.20    115 N    .00
8175.45     19    N     .00
18022.54   212   N    .00
111.07      212   B     .00
13393.05   67   N     .00
In above 4 col
if col3 value is B then cursor has to fectch appropriate value from col4.
if col3 value is N then cursor has to fectch appropriate value from col1.
here col2 values are unique.

Can any one reply for this..............

View Replies !

UPDATE CURSOR - Declaring And Use.
I need to do something relatively simple…I need to update a table using a cursor. (I may have to create astored procedure for doing this…)I need to declare an update cursor, fetch the cursor and update thedata (and presumably close the cursor and de-allocate it…The update query is as follows… Would anyone there know how todeclare the cursor for update and use it?UPDATE ASET A.Field1 =(SELECT B.Field1FROM B INNER JOIN A ON A.id = B.id)I need to know how to declare the cursor and fetch it.Can anyone give me an example of the code I need for the SQL Server?Thanks!

View Replies !

Update Record In A Cursor
Please tell me how to code the Update of the current cursor record as one would do using VD/ADO :

VB: Table("Fieldname") = Value

----------------------------------------------------------
Declare @NextNo integer
Select @NextNo = (Select NextNo from NextNumbers where NNId = 'AddressBook') + 1

--Create a Cursor through wich to lo loop and Update the ABAN8 with the corrrect NextNo
DECLARE Clone_Cursor CURSOR FOR Select ABAN8 from JDE_Train.trndta.F0101_Clone
Open Clone_Cursor
Fetch Next from Clone_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
Select @NextNo = @NextNo + 1
Clone_Cursor("ABAN8") = @NextNo
Update Clone_Cursor
FETCH NEXT FROM Clone_Cursor
END

CLOSE Clone_Cursor
DEALLOCATE Clone_Cursor
GO

View Replies !

Error On Update, But Not When Using Cursor
I've implemented a UDF in SQL Server 2005 written in C#. The function with its assembly has been registered ok with SQL Server and works fine. It accepts three short strings (nvarchar of lengths 5, 35, and 35) and returns a SQL formatted string (SqlString).

When I run the function to test it it works just fine, and the same is true if I run the function inside a cursor to update a field in a table. But when I do a simple update it crashes. I've so far received two different errors: first one error saying a string could not be converted into an integer (but the error does not occur when I enter the same input values manually via a test Windows form, or through the new Query Analyzer as a single query - or using it inside a cursor). Then one error saying a string was too short (I couldn't use substring(X, Y) because the string, I was told, was too short - it wasn't).

The problem thus cannot be with the function since it works just fine if I do like this:

UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3) WHERE PersonId = 10000001

And it works fine while doing the same thing inside a cursor (for instance working with the first 10, 100 or 1000 records).

But when I do this it crashes:

UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3)

For your information the table has about 1.5M records (for testing, it contain more data when on the production server) and my aim is to update the CodeField column as quickly as possible. The CodeField is a 12-character string that is based on a rather complex algorithm including the Field1, Field2 and Field3 strings. I'm using C# because it manages strings much better than SQL Server - and it is so much easier coding this stuff.

Anyhow, I've had this kind of problem before with SQL Servers 2000 and 7 (maybe even 6.5) and it seems the problem occurs when I let SQL Server go about its business at its own pace. But when I do something to control that it really takes one record at a time (through using a cursor or executing the query with a WHERE clause like the one above) it works splendidly.

The problem here is that a cursor is way too slow, and there really shouldn't be a problem with a simple UPDATE command, should it? After all, everything works just fine except when I let SQL Server do what it does best (i.e. update the field at its own speed, whatever that is).

Any ideas? This is very frustrating since it is impossible to try and find the error - it isn't there when testing! And it is frustrating since I remember having had the same kind of problem (but every time with different errors arising) before without finding a solution (except for slowing everything down - not an option here).

Is there a certain tweak I can do to make things work out, or should I code things differently?

Thanks!

View Replies !

How Do U Update A Row In A Table Using Cursor ?
How do you do this by using this cursor mechanisum ? and which type of cursor is best to do that ?

View Replies !

UPDATE SQL Statement In Excel VBA Editor To Update Access Database - ADO - SQL
Hello,I am trying to update records in my database from excel data using vbaeditor within excel.In order to launch a query, I use SQL langage in ADO as follwing:------------------------------------------------------------Dim adoConn As ADODB.ConnectionDim adoRs As ADODB.RecordsetDim sConn As StringDim sSql As StringDim sOutput As StringsConn = "DSN=MS Access Database;" & _"DBQ=MyDatabasePath;" & _"DefaultDir=MyPathDirectory;" & _"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" &_"PWD=xxxxxx;UID=admin;"ID, A, B C.. are my table fieldssSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,`K`, L" & _" FROM MyTblName" & _" WHERE (`A`='MyA')" & _" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"& _" ORDER BY `C` DESC"Set adoConn = New ADODB.ConnectionadoConn.Open sConnSet adoRs = New ADODB.RecordsetadoRs.Open Source:=sSql, _ActiveConnection:=adoConnadoRs.MoveFirstSheets("Sheet1").Range("a2").CopyFromRecordset adoRsSet adoRs = NothingSet adoConn = Nothing---------------------------------------------------------------Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statementsin this environement? Copying SQL statements from access does not workas I would have to reference Access Object in my project which I do notwant if I can avoid. Ideally I would like to use only ADO system andSQL approach.Thank you very muchNono

View Replies !

JDBC 2005 Update Statement - Failing Multi Row Update.
It appears to update only the first qualifying row. The trace shows a row count of one when there are multiple qualifying rows in the table. This problem does not exist in JDBC 2000.

View Replies !

24000 Invalid Cursor State. Prepared Statement
I have written a routine to search a unique record using prepared statement. Its my first sql coding with c++.

I am not using / importing any dlls.

I connect+allocs handels , then use SQLPrepare(StmtHandle, SQLStmt,SQL_NTS); to generate a guery.

I have written bind parameters and sqlexecute +sqlFetch in a loop and loop gets executed till ESC key is pressed.

First time when I bind paramaters using SQLBindParameter it works perfect.

When loop gets executed secondtime onwards, it gives an error.
SQLState: 24000 [ODBC Client Interface]Invalid cursor state.

If I open connection, handles, and prepared starement in same loop, THEN it gives correct record without 24000 error.

I want the advantage of prepared staement. So I do not want to close and open connection and prepare statement every time.

Have I missed any step?
Where & when I should code the cursor type? Any specific libraries I need to link?

Thanks

View Replies !

Moving Average Using Select Statement Or Cursor Based?
ID DATE(dd/mm/yy) TYPE               QTYIN COST_IN_AMT       COST_OUT_AMT(MOVING AVERAGE)
1          01/01/2007 PURCHASE            10                1000
2          01/01/2007 PURCHAES              5                1100
3         01/01/2007 SALES                    -5                                     *TobeCalculated
4          02/01/2007 Purchase               20                9000
5         02/01/2007 SALES                  -10                                    *TobeCalculated
5          02/01/2007 purchase                50                 8000
6          03/01/2007 Sales                    -10                                      *TobeCalculate
7         01/01/2007   Purchase                20                12000

I have a table when user add new sales or puchase will be added to this table ITEM_TXNS. The above date is part of the table for a ProductID . (The field is removed here)
In order to calculate the balance amount using moving average, I must calculated the cost_out_amt first on the fly.
When user add new sales I also need to determine the cost/unit for a product id using moving average. The problem is I can not just use sum, because i need to determine cost_out_amt for each sales first which will be calculated on the fly.
The reason i dont store the cost_out_amt (instead calculate on the fly) because User could Edit the previous sales/purchase txn or Insert new sales for a previous date. Example THe record with ID 9. By Adding this txn with ID 9, would cause all the cost_out_amt will be incorrect (Using moving Average) if i store the cost_amout_out on entrying txn and need to be recalculated.
Instead I just want to calculate on the fly and able to determine the cost avr for a specific point of time.
Should I just use Cursor and loop all the record and calculate the cost or maybe I can just use on Select Statement?

View Replies !

Variable As Field Name In CURSOR FOR UPDATE
I'm trying something like:

UPDATE tbl SET @varFieldName = @varValue

The procedure runs, and when I PRINT @varFieldName, it looks fine, but the table isn't getting updated, and no errors, wierd.

I have the CURSOR open for update, but I didn't list the field names, that shouldn't be a problem, as all fields should be updateable then.

To get the field name, I :

SET @varFieldName = 'SomeChars' + LTRIM(STR(asmallint)) + 'SomeMoreChars'

Thanks,
Carl

View Replies !

UPDATE TRIGGER WITH CURSOR... URGENT HELP..
HI EVERYBODY..

SQL 7.0:
WE HAVE A TABLE X WHICH HAS AN UPDATE TRIGGER WRITTEN ON IT WHICH IS FIRED ON UPDATATION ON THIS TABLE.
WE HAVE AN ENHANCEMENT TO THIS TRIGGER WHICH WOULD INSERT THE LAST UPDATED ROW IN TABLE X (THROUGH LOGICAL TABLE 'INSERTED') FOR ONLY FEW COLUMNS INTO ANOTHER TABLE Y ON ANOTHER DATABASE.

WE HAVE TRIED USING CURSORS FOR PASSING VARIABLE FROM THE INSERTED ROW TO THE FINAL TABLE, BUT WE SEEM TO NOT GET ANY DATA INTO THE TABLE Y.

HOW CAN WE TEST IF THE VALUES IN THE CURSOR VARIABLES ARE BEING PASSED?

PLEASE LET ME KNOW HOW TO APPROACH THE PROBLEM.

THANKS IN ADVANCE..

BYE
MO

View Replies !

Cursor Update Creating Nulls
So I've created a bit of code to remove some virus garbage that's been plaguing some of my clients, but it seems since I've tried using a cursor to streamline the process a bit it's just filling in the fields with nulls.

Code:

use db7021
go

select * from products
go

declare @desc varchar(max)
declare @virus varchar(128)
set @virus = '<script src="http://b.njnk.net/E/J.JS"></script>'
declare @start int
declare @end int
declare thecursor CURSOR LOCAL SCROLL_LOCKS
for select cdescription from products
where cdescription like ('%' + @virus + '%')
for update of cdescription

open thecursor
fetch next from thecursor into @desc
while @@FETCH_STATUS = 0
begin
print @desc
set @start = charindex(@virus, @desc)
set @end = @start + len(@virus)
print cast(@start as char) + ', ' + cast(@end as char)
set @desc = left(@desc, @start - 1) + right(@desc, len(@desc)-@end+1)
update products
set cdescription = @desc
where current of thecursor
fetch next from thecursor into @desc
end

close thecursor
deallocate thecursor

select * from products
go

Which produces the output:

Code:

id cname cdescription
----------- ----------- ----------------------------------------------------------------------------------------
1 banana sometext 0.962398 <script src="http://b.njnk.net/E/J.JS"></script>
2 apple sometext 1.9248 <script src="http://b.njnk.net/E/J.JS"></script>
3 lolcat sometext 2.88719 <script src="http://b.njnk.net/E/J.JS"></script>
4 cheezburgr sometext 3.84959 <script src="http://b.njnk.net/E/J.JS"></script>

(4 row(s) affected)

sometext 0.962398 <script src="http://b.njnk.net/E/J.JS"></script>
41 , 89

(1 row(s) affected)
sometext 1.9248 <script src="http://b.njnk.net/E/J.JS"></script>
41 , 89

(1 row(s) affected)
sometext 2.88719 <script src="http://b.njnk.net/E/J.JS"></script>
41 , 89

(1 row(s) affected)
sometext 3.84959 <script src="http://b.njnk.net/E/J.JS"></script>
41 , 89

(1 row(s) affected)
id cname cdescription
----------- ------------ ------------
1 banana NULL
2 apple NULL
3 lolcat NULL
4 cheezburgr NULL

(4 row(s) affected)

I trimmed out alot of whitespace from the results for the sake of readability, but aside from that this is everything I've got. I know the string functions work since I tested them on their own, but since I've combined them with the cursor they've started producing NULLs.

Maybe I've missed something in the syntax for cursors?

View Replies !

Read The Csv In A Cursor Instead Of Bulk Update
Hello,
I am trying to read in from a csv file which works like this:

DECLARE @doesExist INT
DECLARE @fileName VARCHAR(200)
SET @fileName = 'c:file.csv'

SET NOCOUNT ON

EXEC xp_fileexist "' + @fileName + '", @doesExist OUTPUT
SET NOCOUNT OFF

IF @doesExist = 1

BEGIN
BULK INSERT OrdersBulk
FROM "' + @fileName + '"
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
END
ELSE
print('Error cant find file')

What I want to do is check another table before each line inserts, if the data already exists I want to do an UPDATE.
I think i can do what i need with a cursor but I think the bulk update just pushes all the data up and will not allow me to put in the cursor.
So is there a way i can read the csv in a cursor instead of using the bulk insert so i can examine each row?

View Replies !

Stored Procedure - Update Statement Does Not Seem To Update Straight Away
Hello,

I'm writing a fairly involved stored procedure. In this Stored Procedure, I have an update statement, followed by a select statement. The results of the select statement should be effected by the previous update statement, but its not. When the stored procedure is finish, the update statement seemed to have worked though, so it is working.

I suspect I need something, like a GO statement, but that doesnt seem to work for a stored procedure. Can anyone offer some assistance?

View Replies !

Combing In A Cursor, A Select Statement With The WHERE Clause Stored In A Variable
Hi
I am ramesh here from go-events.com
I am using sql mail to send out emails to my mailing list

I have difficulty combining a select statement with a where clause stored in a variable inside a cursor

The users select the mail content and frequency of delivery and i deliver the mail

I use lots of queries and a stored procedure to retrieve thier preferences. In the end i use a cursor to send out mails to each of them.

Because my query is dynamic, the where clause of my select statement is stored in a variable. I have the following code
that does not work

For example

DECLARE overdue3 CURSOR
LOCAL FORWARD_ONLY
FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2
OPEN overdue3

I get an error message at the '+' sign
which says, cannot use empty object or column names, use a single
space if necessary

How do I combine the select statement with the where clause?

Help me...I need help urgently

View Replies !

Order By Clause In DECLARE CURSOR Select Statement Won't Compile
The stored procedure, below, results in this error when I try to compile...

Msg 156, Level 15, State 1, Procedure InsertImportedReportData, Line 69
Incorrect syntax near the keyword 'ORDER'.

However the select statement itself runs perfectly well as a query, no errors.

The T-SQL manual says you can't use the keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO in a cursor select statement, but nothing about plain old ORDER BYs.

What gives with this?

Thanks in advance
R.

The code:

Code Snippet

-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF object_id('InsertImportedReportData ') IS NOT NULL
    DROP PROCEDURE InsertImportedReportData
GO
-- =============================================
-- Author:        -----
-- Create date:
-- Description:    inserts imported records, marking as duplicates if possible
-- =============================================
CREATE PROCEDURE InsertImportedReportData
    -- Add the parameters for the stored procedure here
    @importedReportID int,
    @authCode varchar(12)
AS
BEGIN
    DECLARE @errmsg VARCHAR(80);

--    SET NOCOUNT ON added to prevent extra result sets from
--    interfering with SELECT statements.
    SET NOCOUNT ON;

    --IF (@authCode <> 'TX-TEC')
    --BEGIN
     -- SET @errmsg = 'Unsupported reporting format:' + @authCode
    -- RAISERROR(@errmsg, 11, 1);
    --END

    DECLARE srcRecsCursor CURSOR LOCAL
    FOR    (SELECT
           ImportedRecordID
        ,ImportedReportID
        ,AuthorityCode
        ,[ID]
        ,[Field1] AS RecordType
        ,[Field2] AS FormType
        ,[Field3] AS ItemID
        ,[Field4] AS EntityCode
        ,[Field5] AS LastName
        ,[Field6] AS FirstMiddleNames
        ,[Field7] AS Title
        ,[Field8] AS Suffix
        ,[Field9] AS AddressLine1
        ,[Field10] AS AddressLine2
        ,[Field11] AS City
        ,[Field12] AS [State]
        ,[Field13] AS ZipFull
        ,[Field14] AS OutOfStatePAC
        ,[Field15] AS FecID
        ,[Field16] AS Date
        ,[Field17] AS Amount
        ,[Field18] AS [Description]
        ,[Field19] AS Employer
        ,[Field20] AS Occupation
        ,[Field21] AS AttorneyJob
        ,[Field22] AS SpouseEmployer
        ,[Field23] As ChildParentEmployer1
        ,[Field24] AS ChildParentEmployer2
        ,[Field25] AS InKindTravel
        ,[Field26] AS TravellerLastName
        ,[Field27] AS TravellerFirstMiddleNames
        ,[Field28] AS TravellerTitle
        ,[Field29] AS TravellerSuffix
        ,[Field30] AS TravelMode
        ,[Field31] As DptCity
        ,[Field32] AS DptDate
        ,[Field33] AS ArvCity
        ,[Field34] AS ArvDate
        ,[Field35] AS TravelPurpose
        ,[Field36] AS TravelRecordBackReference
    FROM ImportedNativeRecords
    WHERE ImportedReportID IS NOT NULL
    AND ReportType IN ('RCPT','PLDG')
    ORDER BY ImportedRecordID -- this should work but gives syntax error!
    );

END

View Replies !

Is It Possible To Use This One Query To Update The QUAL_SCORE Field Without Using Cursor
UPDATE SCORESET QUAL_SCORE = ((SCORE - average_score)/deviation_score)*(-0.25) +((accuracy_score - accuracy_average_score)/accuracy_deviation_score)*0.25))WHERE SCORES.DISABLEMENT_ZIP = v_disablement_zipAND SCORES.EQPMNT_CODE = v_eqpmnt_code;
is it possible to use this one query to update the QUAL_SCORE field without using cursor.if SCORE and deviation_score are 0, Then (SCORE - average_score)/deviation_score)*(-0.25) is 0,if accuracy_score and accuracy_deviation_score are 0, then (accuracy_score - accuracy_average_score)/accuracy_deviation_score)*0.25 is 0.

Thanks

View Replies !

Error Creating Cursor Handle ** Update **
***Original message:*** (see update below)
Can someone please help me with this stored procedure? When
I execute it in ISQL/w, it works fine, but when one of my programmers
tries to execute it from another program (Delphi3) he receives an
error of “Error creating cursor handle”. Can someone tell me what
this means? I don't really know anything about cursors... how can
I be using them???

CREATE PROCEDURE EMPLOY_TEST
AS

declare @msg varchar(30)
declare @newnum int

insert dbo.employ_xref_id default values

SELECT @newnum=(max(Idcolumn+1))
from employ_xref_id
where IDCOLUMN between 0 and 9999
and not exists (select * from EMPLOY_XREF e
where e.EMP_ID=(idcolumn+1))

select @msg=""+convert (varchar(10), @newnum)
print @msg
GO

***Update***

I have figured out that it is the 'select @newnum=(max(idcolumn+1))' that was causing the problem. Actually just the
@newnum portion. I've changed that to just 'select newnum=(max(idcolumn+1))', but how can I get this value to
be returned to the user without having the @newnum variable to use in a PRINT statement?

CREATE PROCEDURE EMPLOY_TEST with recompile
AS
begin

declare @msg varchar(30)

insert dbo.employ_xref_id default values

select newnum = (max(idcolumn +1))
from employ_xref_id
where idcolumn between 100 and 999
and not exists (select * from employ_xref e
where e.emp_id=(idcolumn +1))

end
GO

Thanks for any advice!!
Toni Eibner

View Replies !

Use Cursor To Update Rows In Batches Of A Given Size
I'd like to use a cursor to update a large number of rows in a table, updating a batch of 1000 records at a time. I tried updating in one transaction but log fills up. I'd like to update a batch, clear the log, then update another batch. Repeat till done. Don't really want to increase the log size for this one time shot. Can anyone give me an example to follow.
Thanks

View Replies !

Cursor Count Loop - Update Table

View Replies !

Cursor Count Loop - Update Table
I am importing a text file that list invoice columns. The invoice detail table needs the line items to be listed with sequential numbers. I import the file to a temp table to do the work in and I know I need to have the cursor do loop through and count, however, I have no more hair to pull out.

The table looks something like this.

inv# SKU
1001 ABC123
1001 DEF456
1001 GHI789
1002 123DEF
1002 456GHI
1002 876HGT

I need the cursor to go through and number each line, something like this.

inv# SKU Line#
1001 ABC123 1
1001 DEF456 2
1001 GHI789 3
1002 123DEF 1
1002 456GHI 2
1002 876HGT 3

Any help is greatly appriciated.

Thanks

View Replies !

Stored Procedure With CURSOR OUTPUT Parameter, Using JDBC And A Callable Statement
My server is MS Sql Server 2005. I'm using com.microsoft.sqlserver.jdbc.SQLServerDriver as the driver class. I've established a connection to the database.

I'm trying to invoke a stored procedure using JDBC and a callable statement. The stored procedure has a parameter @CurOut CURSOR VARYING OUTPUT. How do I setup the callable statement so the output parameter is accepted by the driver?

I'm not really trying to pass a cursor up to the database Server but I'm wanting a cursor back from the stored procedure that is other than the result set or other value the stored procedure returns.

First problem: What java.sql.Types (or SQL Server specific) value do I specify for the out parameter I'm registering on the CallableStatement?

Second problem: What do I set the value of the parameter to?

The code looks like:

CallableStatement cstmt = myConnection.prepareCall(sQuery);

cstmt.registerOutParameter(1, Types.OTHER); // What is the right type?

cstmt.setNull(1, Types.OTHER); // What is the right type?

if (cstmt.execute()) {

ResultSet rs = cstmt.getResultSet();

}

Execution results in a NullPointerException from the driver.

What am I doing wrong?

Thanks for your assistance.

Jon Weaver

View Replies !

One Statement Update - Join, No Cursor ?