Have field take value of another field when not explicitly given new value

Question

I have a table in this format:

 id | ....... | other_id
-------------------------
  1 | ....... |    5     
  2 | ....... |    2

Basically, when the form is submitted, sometimes there will be a value for other_id in the form, and the insertion of the new row goes as normal. However, if there is no value given for other_id, I want its value to come from id. The issue is that id is the auto incrementing id, so the actual value of id is unknown until it's actually inserted into the table.

Is there a way to dynamically do this with SQL itself, without having to run additional queries afterward?

fast answer NO, tricky answer check this one http://stackoverflow.com/questions/933565/get-auto-increment-value-with-mysql-query — VeNoMiS, Jul 09 '13 at 15:02
@YourCommonSense I assumed I had a syntax issue but I guess not! I undeleted — ಠ_ಠ, Aug 28 '13 at 21:29

score 1 · Answer 1 · answered Jul 09 '13 at 14:54

1

You can use a insertion trigger:

CREATE TRIGGER foo AFTER INSERT ON TABLENAME FOR EACH ROW
IF NEW.other_id IS NULL THEN
   SET NEW.other_id := NEW.id;
END IF;;

answered Jul 09 '13 at 14:54

jh314

27,144
16
62
82

Thanks - after modifying to correct field names, I get this error in trying to create the trigger: `#1362 - Updating of NEW row is not allowed in after trigger ` Am I supposed to be using `NEW`? – ಠ_ಠ Jul 09 '13 at 15:01

score 0 · Answer 2 · answered Jul 09 '13 at 15:09

0

@jh314 is almost right. Just change AFTER INSERT to BEFORE INSERT

answered Jul 09 '13 at 15:09

Parag

51
1

Well, the id doesn't have a value before the insert. – ಠ_ಠ Jul 09 '13 at 15:13
aka if I changed this to a before insert, it would have a value of 0. – ಠ_ಠ Jul 09 '13 at 15:27

Have field take value of another field when not explicitly given new value

2 Answers2