0

Hi
I have an issue to update data between two table. I want to update data from one table to another using Store
procedure. My code is

SELECT NoMonth,ACC_P,StartDate FROM dbo.tbl_Accural_Basic
UPDATE WEB_tbl_Accural_Audit SET NoMonth=nomonth,ACC_P=ACC_P, StartDate=Startdate

But Nothing happend. It will show the data in select command
Pls help me
Maideen

3
Contributors
2
Replies
25
Views
3 Years
Discussion Span
Last Post by ugi.nagesh
0

Let's first explain why it DIDN'T work then suggest a possible solution.

The SELECT statement is just that. It stands alone inside your stored procedure. It's job is to return a result set.

The UPDATE statement is not related in any way to your SELECT statement. So, if you look carefully, all the UPDATE is doing is setting each named column equal to itself. Therefore, it appears there is no work going on.

Okay, so here's where it gets a little dicey. Since we don't know how the tables dbo.tbl_Accural_Basic and WEB_tbl_Accural_Audit are related, it's really difficult to determine HOW the update is to occur. However, here's a "fake" example that might point you in the right direction:

UPDATE A
SET A.NoMonth = B.NoMonth,
A.ACC_P = B.ACC_P,
A.StartDate = B.StartDate
from dbo.WEB_tbl_Accural_Audit A
inner join dbo.tbl_Accural_Basic B
on A.myPrimaryKeyField = B.myPrimaryKeyField

Note that the update statement refers to the alias "A", and that there must be some way to join the two tables...then you can update the A table with results from the B table.

Another possibility is that there will always and only be one row returned by the B table. In that case, you can declare some local variables, SELECT the values from the B table into them, then use a separate UPDATE statement for the A table and set the values equal to the local variables.

I hope these examples help! Happy coding!

0

Hi Maideen,

find the below update query

UPDATE WEB_TBL_ACCURAL_AUDIT
SET NOMONTH =TBL_ACCURAL_BASIC.NOMONTH ,
    ACC_P=TBL_ACCURAL_BASIC.ACC_P,
    STARTDATE=TBL_ACCURAL_BASIC.STARTDATE
FROM    WEB_TBL_ACCURAL_AUDIT  WEB_TBL_ACCURAL_AUDIT
INNER JOIN  TBL_ACCURAL_BASIC TBL_ACCURAL_BASIC
ON WEB_TBL_ACCURAL_AUDIT.<<KEY COLUMN>>=TBL_ACCURAL_BASIC.<<KEY COLUMN>>

Note:
Key Column : Key column to mapp both the tables

Hope this would solve your problem..

Happy coding..

THanks

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.