update query based on presence of record

  • Thread starter Thread starter Ramesh
  • Start date Start date
R

Ramesh

HI,

a table has customer info with a status field for active/inactive. i need
to make an update query which will set the status as active if the customer
id is present in the order table in the last one year. and set it to
inactive when there is no record in the orders table for a year.

any suggestions on how i could do this query?

thanks
ramesh
 
update customer_info set status=-1*c from customer_info a join (select
b1.costumer_id,count(a1.costumer_id) as c from orders a1 right join
customer_info b1 on a1.customer_id=b1.costumer_id group by b1.customer_id
where xxxxxxxxxxxxxx) b on a.customer_id=b.customer_id

in the xxxxx put the range of date you want to check
 
thanks Lior.

Isnt there a way to do with the Design view or wizard .. am quite unfamiliar
with Sql syntaxes and bit of a struggle here. Or could you guide to a short
way to get to some basics on this?

Thanks
Ramesh
 
Back
Top