CODEWITHSUNDEEP
sqlsql-server-2008
Shaul Zuarets
Shaul Zuarets

Reputation: 849

can't update a field(column in a table) using datediff in the 'where' part

I'm trying to update a field in a table only if it has been a month from the last update.

I tried:

update studentClasses set lastCharge = getDate(), classBalance = classBalance - Price
WHERE DATEDIFF(MONTH, classBalance.lastCharge , GETDATE()) = 1
AND classBalance.endDate < GETDATE()

Didn't work.. :(

Is anyone know how can I do it?

Thanks in advance

(I'm using sql server 2008 express)

Upvotes: 0

Views: 111

Answers (1)

Caleth
Caleth

Reputation: 63039

You are referencing classBalance as both a table and a column in the studentClasses table. Maybe try:

update studentClasses set lastCharge = getDate(), classBalance = classBalance - Price
WHERE DATEDIFF(MONTH, lastCharge , GETDATE()) = 1
AND endDate < GETDATE()

(assuming endDate and Price are in studentClasses and classBalance is a column, not a table)

Upvotes: 1

Related Questions