CODEWITHSUNDEEP
mysql
George
George

Reputation: 31

Update field while inserting non-unique rows using Mysql

While insert, I need to update a given field if the value of another field already exist.

Eg: fieldone | fieldtwo 1 | New 2 | New 1 | Old

Here fieldone is not unique and fieldtwo values should get updated while value of fieldone gets repeated. If again 1 comes in fieldone, we should update the fieldtwo value as 'Old' instead of 'New'.

Is there any way to do this using only Mysql?(with out using any other language)

Many Thanks in advance

Upvotes: 0

Views: 43

Answers (2)

George
George

Reputation: 31

This works at last :)

INSERT INTO table_name(fieldone, fieldtwo) SELECT 'valueone', IF(COUNT(fieldone)=0, 'new', 'old') FROM table_name WHERE fieldone='valueone';

Hope this helps someone.

Thanks all for your replies :)

Upvotes: 0

Prateek099
Prateek099

Reputation: 549

    Insert into table_name values(VAL_FIELDONE ,  
    (SELECT CASE WHEN COUNT(VAL_FIELDONE) = 0 
    THEN 'NEW'  ELSE 'OLD' END))

OR

 Insert into table_name values(VAL_FIELDONE ,  
    (SELECT IF(COUNT(FIELDONE) = 0 ,'NEW','OLD') from table_name where
FIELDONE = VAL_FIELDONE))

Here VAL_FIELDONE is value you want to insert in fieldone column, and filedtwo value will be old or new accordingly. The select will return 'NEW' if fieldone is a new value and 'OLD' otherwise

Upvotes: 1

Related Questions