I needed to update a relational database table’s values from another database table referencing each other and came out with an interesting way to solve this.
The ideas is to generate other SQL as text as output, in my case a series of update statements.
I guess an example could help to explain the whole thing better.
We have 2 tables:
CREATE TABLE FIRST (ID INT, COL1 VARCHAR);
And
CREATE TABLE SECOND (ID INT, COL1 VARCHAR, FIRST_ID INT, FLAG INT);
Let’s insert a row in the second column to get a result
INSERT INTO SECOND (ID, COL1, FIRST_ID, FLAG) VALUES (1,'SOME STUFF',1,1);
Let’s say we want to update the FIRST.COL1 column with the value of SECOND.COL1 column where the SECOND.FLAG column is 1.
We could write something like:
SELECT 'UPDATE FIRST SET COL1=''' || SECOND.COL1 || ''' WHERE ID=' || SECOND.FIRST_ID || ';' AS QUERY FROM SECOND WHERE FLAG = 1;
And the output is this:
UPDATE FIRST SET COL1='SOME STUFF' WHERE ID=1;
As expected!
I guess this technique could be dangerous, but I’ve found this so amusing I had to share.