help with cursor - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help with cursor

Author

Topic

shantanu88d
Starting Member

35 Posts

Posted - 2011-03-25 : 05:04:32

i have this table


t_id    acc_no          bill_type             bill_amt  bill_date               pf_no
4	560001234598765	Mobile Bill	      2000.00	2011-03-15 00:00:00.000	NULL
15	435678766623865	Mobile Bill	       400.00	2011-03-25 00:00:00.000	NULL
16	435678766623865	Mobile Bill	       500.00	2011-03-25 00:00:00.000	NULL
7	704407536688742	Mobile Bill	       199.00	2011-03-16 00:00:00.000	NULL
8	704407536688742	Mobile Bill	       199.00	2011-03-16 00:00:00.000	NULL
9	704407536688742	Mobile Bill	       300.00	2011-03-21 00:00:00.000	NULL
10	704407536688742	Landline Bill	      1000.00	2011-03-21 00:00:00.000	NULL
11	704407536688742	New Purchase Bill	 0.00	2011-03-21 00:00:00.000	NULL
12	560002044543431	Mobile Bill	       800.00	2011-03-21 00:00:00.000	NULL

I want to update this table. I have another table where pf_nos are stored against account_numbers. So from that table i want to add pf_no in this table wherever the account_nos match. How to write such a query ??? I thought cursor can be used here...but i dont knw how to make them work !!

Please help !!

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-25 : 05:29:08

Update A
set A.pf_No=B.pf_No
From ThisTable A
Inner Join ThatTable B on A.Acc_No=B.Acc_No

However make sure that in the other table there is just one pf_No against one account. in other words one to one relation using Acc_No.

Cheers
MIK

shantanu88d
Starting Member

35 Posts

Posted - 2011-03-25 : 05:45:24

I wrote this query


begin tran
update transaction_details
set transaction_details.pf_no = emp_master.pf_no
from transaction_details,emp_master
inner join transaction_details on emp_master.account_no = transaction_details.account_no

but this error comes:
The objects "transaction_details" and "transaction_details" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-03-25 : 07:08:34

It's because you've got transaction details twice in your FROM part of the statement. You shouldn't use the "," cross join syntax, it's depreciated. I think you're not quite sure how to write joins. It should be like this:



begin tran
update transaction_details
set transaction_details.pf_no = emp_master.pf_no
from emp_master
inner join transaction_details on emp_master.account_no = transaction_details.account_no

Look up joins at w3schools.

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-03-25 : 07:09:15

Also, why did you think you'd need a cursor for this? They shoudl be used very sparingly

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-25 : 07:55:50

quote:
Originally posted by michael.appleton

It's because you've got transaction details twice in your FROM part of the statement. You shouldn't use the "," cross join syntax, it's depreciated. I think you're not quite sure how to write joins. It should be like this:

begin tran
update transaction_details
set transaction_details.pf_no = emp_master.pf_no
from emp_master
inner join transaction_details on emp_master.account_no = transaction_details.account_no

Look up joins at w3schools.

A little addition: After executing the above query make sure that the data is updated as per your requirement by checking e.g. Select * From transaction_details and if found to be ok then in the same SQL window dont forget to execute the following command "Commit"

Cheers
MIK

shantanu88d
Starting Member

35 Posts

Posted - 2011-03-25 : 08:02:36

ohhh....yes, I have not used joins yet. very poor at them, you are right. Ok this seems correct. Thanks for help !! BTW, do cursors have severe performace impacts ???

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources