Noob question about a SQL Query

xenon
xenon Member Posts: 30
edited 2015-02-05 in SQL General
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<

Comments

  • ndbcs
    ndbcs Member Posts: 33 
    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
    xenon Member Posts: 30
    Many thanks - perfect.

    =D>
  • xenon
    xenon Member Posts: 30
    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
    xenon Member Posts: 30
    I've worked it out:

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

    Thanks.

Categories