[AccessD] Use a select in an update statement

Mark Breen marklbreen at gmail.com
Thu Aug 6 06:13:53 CDT 2009


Hello John,
See also Michael Maddisons reply.

It is very easy to use temp tables, and one day you will need to do a Update
and will have multiple joins in the tables to be updated, then that SQL
becomes more complex and difficult to get clear in the head.  When that
happens, Mike temp table works a treat,  you can build the big select and
insert into a temp table, then once the hard bit is done you can do a simple
update by joining to the temp table.

In this case Stuarts solution is probably simpler, but store Mikes idea,  it
is the same we all use to do in Access, but linking queries to previously
saved queries.  but it also usually gives great performance for complex
queries.

I have an instinct, let anyone confirm this if it is true, that Oracle heads
use temp tables much more that SQL Server heads.  In fact, I believe that
Cursors are also used extensively in the Oracle world.

Thanks

Mark



2009/8/6 jwcolby <jwcolby at colbyconsulting.com>

> Thanks Stuart.
>
> John W. Colby
> www.ColbyConsulting.com
>
>
> Stuart McLachlan wrote:
> > This won't work before 2005.- before then you couldn't use an expression
> for TOP:
> >
> > declare @topnumber int
> > declare @NoKids int
> >
> > set @topnumber = 10000
> > set @NoKids = 2
> >
> > update tblOrderData
> > set keycode = 'KEYA'
> > where RandomNumber in
> > (Select top (@topnumber) RandomNumber
> >  From tblOrderData
> > Where NoChildren = @NoKids and Keycode is null
> > Order By RandomNumber)
> >
> > On 5 Aug 2009 at 18:31, jwcolby wrote:
> >
> >> Ooops, sorry, SQL Server 2005.
> >>
> >> John W. Colby
> >> www.ColbyConsulting.com
> >>
> >>
> >> Stuart McLachlan wrote:
> >>> Jet SQL or SQL Server?
> >>>
> >> --
> >> AccessD mailing list
> >> AccessD at databaseadvisors.com
> >> http://databaseadvisors.com/mailman/listinfo/accessd
> >> Website: http://www.databaseadvisors.com
> >
> >
> > Stuart McLachlan
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



More information about the AccessD mailing list