0

I am using mysql utility from command line. I would like to update a column value with a given value in any-one of eligible row. along with update I want to print the Primary key of the row where the update has taken place.

Not sure if this is very basic question as I am not very used to Database.

Here are more details:

Table created by:

CREATE TABLE `ExpressionPL` (
 `idExpressionTag` int(11) NOT NULL,
 `date` int(11) NOT NULL,
 `PL` double DEFAULT NULL,
 `TXN` int(11) DEFAULT NULL,
 `Pending` int(11) DEFAULT '0',
 PRIMARY KEY (`idExpressionTag`,`date`)
);

I am using following command to locate eligible row and update the value:

update ExpressionPL
SET Pending = 1500
where Pending=0 AND (PL IS NULL OR TXN IS NULL)
ORDER BY date DESC, idExpressionTag DESC LIMIT 1

Is there a way to print the primary key (idExpressionTag and date) combination where i just updated the value(1500) ?

EDIT: there are multiple processes try to do the same. so i want to be sure that I get associated PKey only to the particular update.

EDIT: as suggested by cantelope. I see following is working for me. I hope this is safe for multi-process environment (I am launching mysql -e command from multiple bash script)

SET @update_id := NULL;
SET @update_date := NULL;
update ExpressionPL
SET Pending = 1800, idExpressionTag = (select @update_id := idExpressionTag), date = (select @update_date := date)
where Pending=0 AND (PL IS NULL OR TXN IS NULL) ORDER BY date DESC, idExpressionTag DESC LIMIT 1;
select @update_id, @update_date;
VK Kashyap
  • 168
  • 1
  • 10
  • 1
    See http://stackoverflow.com/questions/1388025/how-to-get-id-of-the-last-updated-row-in-mysql – cantelope Dec 20 '15 at 19:51
  • thanks for the link. I have updated my Question with the possible solution referring to the link. I get the expected now. I hope this is safe for multi-process invocation. – VK Kashyap Dec 20 '15 at 20:12

0 Answers0