update with cursor

Question

Post reply

update with cursor

carlos cachulo-318532

Say Hey Kid

Points: 697
More actions
March 11, 2008 at 9:47 am

#69989

Hello
I´ve this query to put in a job, the cursor is correct it returns all the data correctely but i is given me an error when updating table CL, appreciate any help!!!
Declare CHEQUES CURSOR SCROLL
FOR SELECT re.no as NO, sum(re.etotal)as EVALOR from re (nolock) where chdata > getdate() group by re.no
Open CHEQUES
Fetch First From CHEQUES
While @@Fetch_Status = 0
BEGIN
update cl.u_resp with EVALOR where cl.no= re.no **** ERROR ****
Fetch NExt from CHEQUES
End
Deallocate CHEQUES

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734498 More actions · Answer 1

Steve Jones - SSC Editor

SSC Guru

Points: 734498

March 11, 2008 at 10:17 am

#788108

What is the error?

carlos cachulo-318532 Say Hey Kid Points: 697 More actions · Answer 2

carlos cachulo-318532

Say Hey Kid

Points: 697

March 11, 2008 at 10:20 am

#788112

hello

it retuns " Invalid column name 'evalor'."

carlos cachulo-318532 Say Hey Kid Points: 697 More actions · Answer 3

sorry, wrong paste

Msg 102, Level 15, State 1, Line 7

Incorrect syntax near 'EVALOR'.

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 4

You can't do it that way... if you insist on doing this the wrong way (with a cursor), then you need to FETCH from the cursor into some variables and then use the variables to drive the update.

It would be MUCH better if you learned up to use joins in the UPDATE statement instead of using a cursor.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

You really don't need a cursor for this. I'd suggest some code, but there are enough errors in your code it's hard to tell what table is getting updated with what. Looks to me that you're using a table alias you haven't declared, and you syntax is a bit all over the place.

How about just describing what you're trying to do, and what you're doing it to (e.g. source and destination tables, etc...).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

carlos cachulo-318532 Say Hey Kid Points: 697 More actions · Answer 6

i.m a newbie at this,

so lets start from the begining, i´ve this two tables CL and RE

In the CL table í´ve this field CL.U_RESP witch needs to by update by the result of this - SELECT re.no as NO, sum(re.etotal)as EVALOR from re (nolock) where chdata > getdate() group by re.no

i nedd a cycle to do this update , if the select to table RE retuns a value then i need to update CL when RE.NO = CL.NO

carlos cachulo-318532 Say Hey Kid Points: 697 More actions · Answer 7

i started with this

update cl

set u_resp= (SELECT re.no as NO, sum(re.etotal)as EVALOR from re (nolock) where chdata > getdate() group by re.no)

from cl (nolock),re (nolock)

where cl.no = re.no

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

Forget loops. You're in SQL server now, not in procedural code.

update CL

set u_resp=EVALOR

from cl

inner join (

SELECT re.no as NO,

sum(re.etotal)as EVALOR

from re (nolock)

where chdata > getdate()

group by re.no) re_grp

on cl.no=re_grp.no

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

carlos cachulo-318532 Say Hey Kid Points: 697 More actions · Answer 9

could help!!

SELECT re.no as NO, sum(re.etotal)as EVALOR from re (nolock) where chdata > getdate() group by re.no

* RETURNS THIS*

NO EVALOR

--------------------------------------- ---------------------------------------

1 1447.160000

49 1644.380000

52 497.060000

68 1421.200000

69 12500.000000

and when i do this - select no,u_resp from cl * RETURNS THIS*

no u_resp

--------------------------------------- ---------------------------------------

1 1447.16

3 108.90

68 1421.20

69 12500.00

102 1416.99

i need to update de CL.U_RESP with RE.EVALOR when CL.NO=RE.NO

but i having dificulties, please help me

carlos cachulo-318532 Say Hey Kid Points: 697 More actions · Answer 10

Matt Miller (3/11/2008)
Forget loops. You're in SQL server now, not in procedural code.
update CL
set u_resp=EVALOR
from cl
inner join (
SELECT re.no as NO,
sum(re.etotal)as EVALOR
from re (nolock)
where chdata > getdate()
group by re.no) re_grp
on cl.no=re_grp.no

THANK YOU VERY MUCH, IT WORKED JUST FINE!!!

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 11

carlos cachulo (3/11/2008)
THANK YOU VERY MUCH, IT WORKED JUST FINE!!!

welcome, happy to help!

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?