Noob question about a SQL Query

xenon

Hi all.

I think this is a fairly basic query but I mainly concern myself with general admin (backups etc) from the management studio.

Basically, I want to select a load of items from the Sales Price Table which are Customer Prices and have Expired. When I find them I want to set a custom field (Blocked Pending Price Check) on the corresponding item on the Item Table

My script to select the items in question is simple enough (and gives the desired result):

select [Item No_] from [MyCompany$Sales Price]
where [Ending Date] < '04/02/2015 00:00:00.000' and [Sales Type] = 0
and [Sales Code] = 'CustomerA'

This gives a list of all items that I want to set the flag on but I'm guessing I need to assign the above results to some sort of variable (let's call it 'A') such that I can do something along the lines of:

update [MyCompany$Item]
set [Blocked Pending Price Check] = 1 where No_ = A

Any help appreciated! [-o<

ndbcs

update [MyCompany$Item]
set [Blocked Pending Price Check] = 1 
where No_  in
(
  select [Item No_] from [MyCompany$Sales Price]
  where 
    [Ending Date] < '04/02/2015 00:00:00.000' 
    and [Sales Type] = 0
    and [Sales Code] = 'CustomerA'
)

xenon

Many thanks - perfect.

=D>

xenon

Also - one other question

Can I use an argument like TODAY() in Excel so that rather than typing the date longhand as in:

where
[Ending Date] < '04/02/2015 00:00:00.000'

Can I somehow say where [Ending Date] < "TODAY" so that I can run this at any time (or even scheduled) without having to edit the query?

xenon

I've worked it out:

where [Ending Date] < (getdate())....

Thanks.