Inner join in an update

29 Sep 2005 1:40 PM

Arne

How do I translate this statement from Access SQL to MS SQL:
UPDATE    Market INNER JOIN
                      MarketSys ON Market.MarketID = MarketSys.CurrentmarketID
SET              Market.EndPeopleRegDt = '1/1/2006'

29 Sep 2005 1:45 PM

SQL

UPDATE m SET EndPeopleRegDt = '1/1/2006'
From Market m INNER JOIN
MarketSys ms ON m.MarketID = ms.CurrentmarketID

http://sqlservercode.blogspot.com/

Show quote

"Arne" wrote:

> How do I translate this statement from Access SQL to MS SQL:
> UPDATE    Market INNER JOIN
>                       MarketSys ON Market.MarketID = MarketSys.CurrentmarketID
> SET              Market.EndPeopleRegDt = '1/1/2006'

29 Sep 2005 1:53 PM

R.D

TRY
UPDATE m SET EndPeopleRegDt = '1/1/2006'
From Market m WHERE m.MarketID = (SELECT ms.CurrentmarketID FROM MarketSys
ms WHERE ms.CurrentmarketID = m.MarketID)
Regards
R.D

Show quote

"SQL" wrote:

> UPDATE   m SET    EndPeopleRegDt = '1/1/2006'
> From Market m INNER JOIN
>                       MarketSys ms ON m.MarketID = ms.CurrentmarketID
>
>
> http://sqlservercode.blogspot.com/
>
>
> "Arne" wrote:
>
> > How do I translate this statement from Access SQL to MS SQL:
> > UPDATE    Market INNER JOIN
> >                       MarketSys ON Market.MarketID = MarketSys.CurrentmarketID
> > SET              Market.EndPeopleRegDt = '1/1/2006'

29 Sep 2005 1:57 PM

R.D

oops
try this
UPDATE markets SET EndPeopleRegDt = '1/1/2006'
WHERE markets.MarketID = (SELECT ms.CurrentmarketID FROM MarketSys ms
WHERE ms.CurrentmarketID = markets.MarketID)

Show quote

"SQL" wrote:

> UPDATE   m SET    EndPeopleRegDt = '1/1/2006'
> From Market m INNER JOIN
>                       MarketSys ms ON m.MarketID = ms.CurrentmarketID
>
>
> http://sqlservercode.blogspot.com/
>
>
> "Arne" wrote:
>
> > How do I translate this statement from Access SQL to MS SQL:
> > UPDATE    Market INNER JOIN
> >                       MarketSys ON Market.MarketID = MarketSys.CurrentmarketID
> > SET              Market.EndPeopleRegDt = '1/1/2006'

29 Sep 2005 2:22 PM

David Portas

UPDATE market
SET endpeopleregdt = '20060101'
WHERE EXISTS
(SELECT *
FROM marketsys AS ms
WHERE ms.currentmarketid = market.marketid) ;

--
David Portas
SQL Server MVP
--

Show quote

"Arne" wrote:

> How do I translate this statement from Access SQL to MS SQL:
> UPDATE    Market INNER JOIN
>                       MarketSys ON Market.MarketID = MarketSys.CurrentmarketID
> SET              Market.EndPeopleRegDt = '1/1/2006'