jmsf jmsf - 2 months ago 8
MySQL Question

If value = 0000 Insert and set value with max()+1

I need to get the maximum value +1 for a field, if that filed as the 0000 value.

IF
(acc_id = 0000)
FROM
crm_accounts;
INSERT
INTO
crm_accounts(acc_id)
SELECT MAX
(acc_id) + 1
FROM
crm_accounts;

Answer

I think you're looking to do an UPDATE statement...

This should get it done for you:

SELECT @id := MAX(acc_id) FROM crm_accounts;
UPDATE crm_accounts
SET acc_id = @id := @id + 1
WHERE acc_id = 0000;