September 1, 2009 at 2:02 pm
It's been years since I wrote any stored procedures - so I'm in desperate need of help. I'm hoping this will be incredibly easy for those of you who write stored procedures on a regular basis.
I have a table (let's call it "MYDATA") that I want to update 4 columns of data. The data that I want to update it to is in another table - a temp table that someone else's application is creating(let's call it "TEMPTABLE") . The tables need to be joined on MYDATA.ERRORNUM and TEMPTABLE.ERRORRECORD and only update records where MYDATA.Project_ID=22
Here are the fields in the table
MYDATA
Errornum
Date (update to TEMPTABLE.DATE)
Description (update to TEMPTABLE.DESCRIPTION)
User (update to TEMPTABLE.USER)
Field1
Field2
SystemType (update to TEMPTABLE.SYSTEM)
Field3
Project_ID
TEMPTABLE
ErrorRecord
Date
Description
User
System
Can anyone help me write a stored procedure that would do this update?
Thanks in advance!!!!
September 1, 2009 at 2:05 pm
Hi
Try this (untested)
UPDATE m SET
m.Project_ID = 22
FROM TEMPTABLE t
JOIN MYDATA m ON t.ERRORNUM = ERRORRECORD
Greets
Flo
September 1, 2009 at 2:08 pm
Sorry - I should have noted there are a couple extra fields in the MYDATA table that I don't care about - so it isn't a one to one push of data.
September 1, 2009 at 2:10 pm
THis link has a good article to begin with.
http://www.bennadel.com/blog/938-Using-A-SQL-JOIN-In-A-SQL-UPDATE-Statement-Thanks-John-Eric-.htm
eDIT: Also, I guess in a hurry, Florian has put the where statement to set.
m.Project_ID = 22 should be in the where clause.
September 1, 2009 at 2:15 pm
Hmm.....that link isn't helping me - it's looking like a foreign language to me. I guess I could figure out how to set a column to "1" based on certain criteria from that link.....but I don't know how to pass data from one table to another. I'm completely lost.
September 1, 2009 at 2:17 pm
Sorry, I miss read your post at first time... Try this
UPDATE m SET
m.Date = t.Date
,m.Description = t.Description
,m.User = t.User
,m.SystemType = t.System
FROM TEMPTABLE t
JOIN MYDATA m ON t.ERRORNUM = ERRORRECORD
WHERE m.Project_ID = 22
September 1, 2009 at 2:24 pm
Florian Reischl (9/1/2009)
Sorry, I miss read your post at first time... Try this
UPDATE m SETm.Date = t.Date
,m.Description = t.Description
,m.User = t.User
,m.SystemType = t.System
FROM TEMPTABLE t
JOIN MYDATA m ON t.ERRORNUM = ERRORRECORD
WHERE m.Project_ID = 22
OK - figured out my little issue - it works now! Thank you so much!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply