Prev: xp_cmdshell sql 2008
Next: Newbie Installing AdventrueWorksDW2008 in another Drive
From: SF on 7 Dec 2009 21:51
Hi,

I am from Access background. I am stuck with write store procedure in SQL.
How do I convert Access update statement below into SQL update one

UPDATE tblProject INNER JOIN tblProjectWorkplan ON tblProject.Pr_ObjectID =
tblProjectWorkplan.Pjw_Pr_ObjectID SET tblProjectWorkplan.Owner = "Ratha"
WHERE (((tblProject.Pr_Year)=2008) AND ((tblProject.Pr_ProvinceID)=2));

SF


From: Michael Coles on 7 Dec 2009 22:45
Try something like this (***untested***):

UPDATE tblProjectWorkPlan
SET Owner = 'Ratha'
WHERE Pjw_Pr_ObjectID =
(
SELECT t.Pr_ObjectID
FROM tblProject t
WHERE t.Pr_Year = 2008
AND t.Pr_ProvinceID = 2
);

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------

"SF" <samnangs(a)pactcambodia.org> wrote in message
news:OKIm2H7dKHA.4636(a)TK2MSFTNGP04.phx.gbl...
> Hi,
>
> I am from Access background. I am stuck with write store procedure in SQL.
> How do I convert Access update statement below into SQL update one
>
> UPDATE tblProject INNER JOIN tblProjectWorkplan ON tblProject.Pr_ObjectID
> = tblProjectWorkplan.Pjw_Pr_ObjectID SET tblProjectWorkplan.Owner =
> "Ratha"
> WHERE (((tblProject.Pr_Year)=2008) AND ((tblProject.Pr_ProvinceID)=2));
>
> SF
>

From: Plamen Ratchev on 7 Dec 2009 22:46
The following update statement should be equivalent:

UPDATE tblProjectWorkplan
SET [Owner] = 'Ratha'
WHERE EXISTS(SELECT *
FROM tblProject AS P
WHERE P.Pr_ObjectID = tblProjectWorkplan.Pjw_Pr_ObjectID
AND P.Pr_Year = 2008
AND P.Pr_ProvinceID = 2);

Here is another version using the SQL Server specific update with join:

UPDATE tblProjectWorkplan
SET [Owner] = 'Ratha'
FROM tblProjectWorkplan AS W
JOIN tblProject AS P
ON P.Pr_ObjectID = W.Pjw_Pr_ObjectID
WHERE P.Pr_Year = 2008
AND P.Pr_ProvinceID = 2;

--
Plamen Ratchev
http://www.SQLStudio.com
From: Michael Coles on 7 Dec 2009 22:56
Oops, typo. Here's a better version:

UPDATE tblProjectWorkPlan
SET Owner = 'Ratha'
WHERE Pjw_Pr_ObjectID IN
(
SELECT t.Pr_ObjectID
FROM tblProject t
WHERE t.Pr_Year = 2008
AND t.Pr_ProvinceID = 2
);

Or Plamen's EXISTS version, which should be equivalent.

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------

"Michael Coles" <admin(a)geocodenet.com> wrote in message
news:OKkRTk7dKHA.3792(a)TK2MSFTNGP02.phx.gbl...
> Try something like this (***untested***):
>
> UPDATE tblProjectWorkPlan
> SET Owner = 'Ratha'
> WHERE Pjw_Pr_ObjectID =
> (
> SELECT t.Pr_ObjectID
> FROM tblProject t
> WHERE t.Pr_Year = 2008
> AND t.Pr_ProvinceID = 2
> );
>
> --
> Thanks
>
> Michael Coles
> SQL Server MVP
> Author, "Expert SQL Server 2008 Encryption"
> (http://www.apress.com/book/view/1430224649)
> ----------------
>
> "SF" <samnangs(a)pactcambodia.org> wrote in message
> news:OKIm2H7dKHA.4636(a)TK2MSFTNGP04.phx.gbl...
>> Hi,
>>
>> I am from Access background. I am stuck with write store procedure in
>> SQL. How do I convert Access update statement below into SQL update one
>>
>> UPDATE tblProject INNER JOIN tblProjectWorkplan ON tblProject.Pr_ObjectID
>> = tblProjectWorkplan.Pjw_Pr_ObjectID SET tblProjectWorkplan.Owner =
>> "Ratha"
>> WHERE (((tblProject.Pr_Year)=2008) AND ((tblProject.Pr_ProvinceID)=2));
>>
>> SF
>>
>

From: SF on 7 Dec 2009 23:14
Thank you very much for your tips.

SF
"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:9eWdnaG8HYueUYDWnZ2dnUVZ_oti4p2d(a)speakeasy.net...
> The following update statement should be equivalent:
>
> UPDATE tblProjectWorkplan
> SET [Owner] = 'Ratha'
> WHERE EXISTS(SELECT *
> FROM tblProject AS P
> WHERE P.Pr_ObjectID = tblProjectWorkplan.Pjw_Pr_ObjectID
> AND P.Pr_Year = 2008
> AND P.Pr_ProvinceID = 2);
>
> Here is another version using the SQL Server specific update with join:
>
> UPDATE tblProjectWorkplan
> SET [Owner] = 'Ratha'
> FROM tblProjectWorkplan AS W
> JOIN tblProject AS P
> ON P.Pr_ObjectID = W.Pjw_Pr_ObjectID
> WHERE P.Pr_Year = 2008
> AND P.Pr_ProvinceID = 2;
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com


 |  Next  |  Last
Pages: 1 2
Prev: xp_cmdshell sql 2008
Next: Newbie Installing AdventrueWorksDW2008 in another Drive