jeudi 29 octobre 2015

SQL Lite: copy rows with a 'dynamic' field value

My first question here. I've been searching all over for an answer but I can't find it...

Here's the problem: I want to Insert multiple records into a table (a sort of copy, but with some differences) I'm leaving the ID blank, so the auto increment will fill it, but I wanted to update a field (let's call it field4) with a diferent value for each result in a SELECT. Let me put this into code:

INSERT INTO ContentTable (field2,field3,field4) SELECT field2,field3,(SELECT ID FROM HolderTable WHERE field5=200) FROM ContentTable WHERE field4 IN (SELECT ID FROM HolderTable WHERE field5=100)

This is resulting in correctly copying all rows, but the field4 is always the same (first row from the Select query). I want it be all the values from the query for each row.

If this looks too confusing here's what I wanna do:

copy into table1 all rows (from table1) where the field5 in table2=100; but also, for each new row on table1 the field4 should be equal to the ID of each row from table2 where field5 in table2=200

Is this somehow possible?

Aucun commentaire:

Enregistrer un commentaire