0

I need Primary keys of the affected rows to be returned after updating a MYSQL table using my PHP Code. Is there anyway to achieve this?

gvm
  • 1,153
  • 3
  • 12
  • 19
  • If you've updated existing records, then you must have identified them somehow - so your invoking (PHP) code would already know some sort of key; if it's not the PK but you want to fetch the PK, you could `SELECT ... WHERE` to obtain it. Or when you say "updating", are you in fact inserting *new* records into the table? – eggyal May 19 '12 at 07:47
  • I don't think there is a way to do this. You'll just have to execute a SELECT query afterwards. – Connor Peet May 19 '12 at 07:48
  • So far tried mysql_affected_rows(); mysql_info(); – gvm May 19 '12 at 07:48
  • And what is your query to perform the update? – Zuul May 19 '12 at 07:49
  • @Zuul Update table set status='E' where status='O' – gvm May 19 '12 at 07:50
  • @eggyal No, just changing the status field with Update statement, not inserting new records – gvm May 19 '12 at 07:52

2 Answers2

2

You will need to fetch the primary keys before performing the UPDATE:

SELECT primary_key_columns FROM my_table WHERE status = 'O';
UPDATE my_table SET status = 'E' WHERE status = 'O';

However, if you have concurrent connections that might alter my_table between the two statements, it's possible that the results of the keys returned by the first SELECT statement will not fully match the records updated by the second UPDATE statement.

To prevent this, you would need to perform the operations in a transaction (but note that transactions require support from your storage engine - InnoDB is usually used for this purpose):

START TRANSACTION;
SELECT primary_key_columns FROM my_table WHERE status = 'O' FOR UPDATE;
UPDATE my_table SET status = 'E' WHERE status = 'O';
COMMIT;
eggyal
  • 122,705
  • 18
  • 212
  • 237
1

One way of doing what you want is previously SELECT the primary keys with the conditions for the update BEFORE you do the update.

If your query is

Update * from "table" where Y

you would have to do the following:

Select "Primary key" from "table" where Y

Update X from "table" where Y

Hope this helps!

jjgl
  • 387
  • 1
  • 5
  • 10