CODEWITHSUNDEEP
mysqlsql-updatemariadb
Phoenix
Phoenix

Reputation: 1528

How to updated a column value, with the help of selected row rows in MariaDB?

I'm new to MariaDB.

I've a table. Each rows has its own validity.

CREATE TABLE `tariff_table` (
  `transportation_id` int(11) NOT NULL AUTO_INCREMENT,
  `transporter` varchar(300) NOT NULL,
  `valid_upto` date NOT NULL,
  `expired_status` int(11) NOT NULL DEFAULT '0',
  `status` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`transportation_id`)
  )

Table Result :

tariff_id || transporter || valid_upto || expired_status || status   
------------------------------------------------------------------
1         || Raj         || 2014-12-08 ||             0  ||     0
2         || Ram         || 2015-01-10 ||             0  ||     0
3         || Mani        || 2014-03-06 ||             0  ||     0
4         || Mano        || 2015-04-15 ||             0  ||     0

My Requirement is, how can i update the expired status using the select query.

select tariff_id from tariff_table where valid_upto <= DATE(now());

Select Query return 2 rows.

tariff_id
---------
1
3

With the help of id, i need to update as like as below.

I Need the below result.

tariff_id || transporter || valid_upto || expired_status || status   
------------------------------------------------------------------
1         || Raj         || 2014-12-08 ||             1  ||     0
3         || Mani        || 2014-03-06 ||             1  ||     0

Help Me.

Upvotes: 2

Views: 2474

Answers (2)

Aziz Shaikh
Aziz Shaikh

Reputation: 16524

You may use the UPDATE statement, like this:

UPDATE `tariff_table`
SET `expired_status` = 1
WHERE valid_upto <= DATE(now());

Upvotes: 3

Saravana Kumar
Saravana Kumar

Reputation: 3729

Use IN keyword.

UPDATE tariff_table SET expired_status = 1 WHERE tariff_id 
IN(select tariff_id from tariff_table where valid_upto <= DATE(now()))

OR

UPDATE tariff_table SET expired_status = 1 WHERE valid_upto <= DATE(now())

Upvotes: 2

Related Questions