Updating tables
-
This seems like a basic thing to me, but I can't get it to work!
I have a tblProject, which has a status of "A" and "I". In order for some calculations to be done, the status for all needed to be set to "A". I made a temporary table of all those with an original status of "I", and then changed the status in tblProject to "A" for every record. Now the calcuations are complete, I need to make all those that were originally "I" back to "I" again, using my temporary table. Here is the code I am trying to use:
update tblProject, tblTemp_Project
set tblProject.status = 'I'
where tblProject.project = tblTemp_Project.project
This fails on the first line "...Incorrect syntax near "tblTemp_Project"...." and I am not really sure why. I tried just referencing the table I wanted to update, but still got errors...any ideas?
I have also tried doing the update like this:
update pjproj p
set p.status_pa =
(select t.status_pa from temp_active_proj t
where t.project = p.project)
which also doesn't work. Any suggestions??
Thanks!
-
You could try this with or without the WHERE clause - I don't know how big your table is and whether this will help..
UPDATE tblProject SET
tblProject.status = 'I'
FROM
INNER JOIN tblTemp_Project ON( tblProject.project = tblTemp_Project.project)
WHERE tblTemp_Project.status = 'I'
I wasn't born stupid - I had to study.
-
Hmmm, I tried that, but still get an "incorrect syntax" error...I think there must be something really obvious that I am just missing
The main table has just over 1000 records and the temp table about 600...
-
UPDATE tblProject SET
tblProject.status = 'I'
FROM dbo.tblProject
INNER JOIN tblTemp_Project ON( tblProject.project = tblTemp_Project.project)
WHERE tblTemp_Project.status = 'I'
-
AAhhhhh......thank you so much!
-
NP. That's why I find it usefull to use the check syntax button before posting a solution
. -
Normally I use the parse button before trying to submit anything, and it didn't see a problem with that! Oh well, thanks again!
-
My comment was a little more intended for Farrell
, 
! -
I did not even see that I forgot a table after the FROM. Well, someone needed to open themselves up for a little fun at their expense - guess that would be me...
I wasn't born stupid - I had to study.
-
If that can make you feel better, then Thanx
.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply