CODEWITHSUNDEEP
mysql
MaryCoding
MaryCoding

Reputation: 664

Update query - incrementing int field value and if statement

I am currently learning SQL through my local mysql db. I have a table named transactions that has 5 fields. I am running an update query for column where name = jane. Essentially, I want to integrate an if statement when date_created – tran_date = 1 month to reset values to transaction = 0, tran_date = 0000-00-00, and change date_created to the new current date.

Query(help)- UPDATED 

UPDATE transactions SET transactions = transactions + 1, tran_date = CURDATE()  WHERE name = 'jim'

Create tables and set values:

CREATE TABLE transactions
(
  id int auto_increment primary key,
  date_created DATE,
  name varchar(20),
  transactions int(6), 
  tran_date DATE
);

INSERT INTO transactions
(date_created, name, transactions, tran_date)
VALUES
(NOW(), 'jim', 0, 0000-00-00),
(NOW(), 'jane', 0, 0000-00-00);

Upvotes: 0

Views: 59

Answers (1)

Jens
Jens

Reputation: 69450

Your updatesyntax is wrong:

UPDATE transactions SET transactions = transactions + 1, tran_date = CURDATE()  WHRE name = 'jim'

You have not to use AND in set clause. You must use a comma at this place.

Upvotes: 1

Related Questions