CODEWITHSUNDEEP
mysql
Daina Hodges
Daina Hodges

Reputation: 853

Update field with another auto increment field value MySQL

I have a table in MYSQL database with two fields:

  1. Id (auto increment field).
  2. Post_Id.

When I insert a new record both fields should have the same value. So I should update post_id with Id value, and at the same time make sure that I update the field with the right value not with any other new inserted record value.

I tried this SQL statement but it was very slow and I was not sure that I select the right value:

set @auto_id := (SELECT AUTO_INCREMENT 
                FROM INFORMATION_SCHEMA.TABLES
                WHERE TABLE_NAME='table_name'
                  AND TABLE_SCHEMA=DATABASE() ); 

update table_name set post_id= @auto_id where id=@auto_id ;

I don't have long experience with MySQL and I cannot change the table structure .

Upvotes: 0

Views: 2106

Answers (2)

lagripe
lagripe

Reputation: 764

you can use triggers :

CREATE TRIGGER UpdatePOST_id
BEFORE INSERT ON table_db
FOR EACH ROW 
SET NEW.post_id := (select id from table_db order by id DESC LIMIT 1)+1 ;

from now on, whatever you insert your as a value in post_id column will be replaced with the id inserted automatically.

Test :

    |id|post_id|
    |20|  20   |
    |21|  21   |
    |22|  22   |
    |23|  23   |

To drop the trigger :

DROP trigger UpdatePOST_id

Upvotes: 0

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30545

The approach you followed is not transaction safe as well.

The best option I can think about is to use trigger

Edit: According to @lagripe's mentionings

CREATE TRIGGER sometrigger
AFTER INSERT ON sometable
BEGIN
  SET NEW.post_id := (SELECT id from sometable order by DESC limit 1) + 1 ; // you may need +1 here. I couldn't test it.
END

or you may consider to use LAST_INSERT_ID

insert into table_name values ( .... );
update table_name set post_id = LAST_INSERT_ID();

but why do you need two columns with the same id at first place?

if you really need why don't you use computed/generated columns?

CREATE TABLE Table1(
  id DOUBLE,
  post_id DOUBLE as (id)
);

Upvotes: 1

Related Questions