update query based on presence of record

[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

 

[Lior Montia]

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

 

[Ramesh]

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