Member Avatar for Maideen

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
  • 200 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by ugi.nagesh
Member Avatar for BitBlt

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!

Member Avatar for ugi.nagesh

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.