INSERT INTO vs UPDATE SET
Posted: Fri Jan 08, 2016 12:04 am
Ok, here's a question for the SQL guru's in the crowd (my knowledge is only cursory... oh gawd, did I just make an SQL joke? Never mind!)
I have some perfectly good UPDATE code that looks like this
UPDATE file SET "var1"=:1, "var2"=:2, "var3"=:3 etc where SOME_ID = X, "array-of-values"
and, using revExecuteSQL, LC tosses the values into the appropriate columns in the database. That works fine.
I have some perfectly good INSERT code that looks like this
INSERT INTO file ("var1","var2","var3" etc) values(:1,:2,:3 etc), "array-of-values"
and again LC tosses the right array values into the right database columns.
But I am bothered by the lack of symmetry in these two SQL statements. I have tried to make INSERT INTO look like UPDATE SET but revExecuteSQL complains (and I am not sure why).
What I did was simply write
INSERT INTO file "var1"=:1, "var2"=:2, "var3"=:3 etc, "array-of-values"
So I have two questions. 1. Is it possible for these two statements to share common formatting? 2. And if not, is there a reason why not? Just so you know there is a reason behind wanting to find as much commonality as possible; I am writing code to write this code. Ie. I want it to be machine generated. The more "in common" it is, the fewer "exceptions" I have to code for. If SQL is just "arbitrarily different" in these two instances, I can appreciate that (par for the course with most computer languages). I just want to be sure I am not missing something obvious.
Thanks for any insights you may have into this.
Mark
I have some perfectly good UPDATE code that looks like this
UPDATE file SET "var1"=:1, "var2"=:2, "var3"=:3 etc where SOME_ID = X, "array-of-values"
and, using revExecuteSQL, LC tosses the values into the appropriate columns in the database. That works fine.
I have some perfectly good INSERT code that looks like this
INSERT INTO file ("var1","var2","var3" etc) values(:1,:2,:3 etc), "array-of-values"
and again LC tosses the right array values into the right database columns.
But I am bothered by the lack of symmetry in these two SQL statements. I have tried to make INSERT INTO look like UPDATE SET but revExecuteSQL complains (and I am not sure why).
What I did was simply write
INSERT INTO file "var1"=:1, "var2"=:2, "var3"=:3 etc, "array-of-values"
So I have two questions. 1. Is it possible for these two statements to share common formatting? 2. And if not, is there a reason why not? Just so you know there is a reason behind wanting to find as much commonality as possible; I am writing code to write this code. Ie. I want it to be machine generated. The more "in common" it is, the fewer "exceptions" I have to code for. If SQL is just "arbitrarily different" in these two instances, I can appreciate that (par for the course with most computer languages). I just want to be sure I am not missing something obvious.
Thanks for any insights you may have into this.
Mark