# Duplicate a table row that is linked to another table with foriegn keys **URL:** \ **Category:** Ignition\ **Created:** [April 3, 2019, 10:15pm UTC](https://forum.inductiveautomation.com/t/duplicate-a-table-row-that-is-linked-to-another-table-with-foriegn-keys/23834 "2019-04-03T22:15:39Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![bellm](https://avatars.discourse-cdn.com/v4/letter/b/b5e925/32.png) [@bellm](https://forum.inductiveautomation.com/u/bellm)\ **Post date:** [April 3, 2019, 10:15pm UTC](https://forum.inductiveautomation.com/t/duplicate-a-table-row-that-is-linked-to-another-table-with-foriegn-keys/23834/1 "2019-04-03T22:15:39Z")
Our customer has asked if we could add a copy button to our recipe screen. What they want is to select a row and press a copy button to duplicate that row and all the coat info. I have attached a screen shot of the screen as it is. Along with screen shots of the two tables that make up the recipes. Ignition Screen: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/6/6874b5578d5384935b90a52791345545ae4b49b4.png) Recipe Table: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/6/63a415b86471602fd2aa4b1dfe11cd6f6a1b947e.png) Coats Table: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/3/38c477606170ef9d7588b64e8f7c3512d83f5572.png) When the user hits copy, recipe 11 would be duplicated. to do this I need to be able to: generate the next available recipes\_id and the next available recipe number and I also need to be able to duplicate all the coats that have a recipes\_id of 39 and replace that with the new recipes\_id. I am struggling to come up with a solution on how to do it and was hoping somebody here would have some insight. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [April 4, 2019, 12:08am UTC](https://forum.inductiveautomation.com/t/duplicate-a-table-row-that-is-linked-to-another-table-with-foriegn-keys/23834/2 "2019-04-04T00:08:35Z")
Generally, you would use two `INSERT .... SELECT ....` queries. The first would duplicate the recipe row, and the other would duplicate the coats. I would allow the recipe id to auto-increment, and retrieve it in the first operation. You’ll have to list all the columns to insert in each case, omitting the auto-increment column in the first, and supplying the returned constant as the recipe ID in the second. ---
**Author:** ![bellm](https://avatars.discourse-cdn.com/v4/letter/b/b5e925/32.png) [@bellm](https://forum.inductiveautomation.com/u/bellm)\ **Post date:** [April 4, 2019, 1:05am UTC](https://forum.inductiveautomation.com/t/duplicate-a-table-row-that-is-linked-to-another-table-with-foriegn-keys/23834/3 "2019-04-04T01:05:48Z")
Thank you, how would I go about retrieving the recipe id during the first operation? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [April 4, 2019, 1:45am UTC](https://forum.inductiveautomation.com/t/duplicate-a-table-row-that-is-linked-to-another-table-with-foriegn-keys/23834/4 "2019-04-04T01:45:11Z")
Typically using the `getKey` option to [system.db.runPrepUpdate()](https://docs.inductiveautomation.com/display/DOC79/system.db.runPrepUpdate) or your DB’s equivalent. ---
**Author:** ![bellm](https://avatars.discourse-cdn.com/v4/letter/b/b5e925/32.png) [@bellm](https://forum.inductiveautomation.com/u/bellm)\ **Post date:** [April 4, 2019, 2:35am UTC](https://forum.inductiveautomation.com/t/duplicate-a-table-row-that-is-linked-to-another-table-with-foriegn-keys/23834/5 "2019-04-04T02:35:53Z")
Ok so internet is spotty on the job site so I am trying to write this offline in my hotel but this is the rough code that I came up with: ```auto newnumber = ... date=... selectID=... Query = "INSERT INTO recipes (Number, Description, DateCreated) SELECT (?, Description, ?) FROM recipes WHERE recipes_id = ?" id = system.db.runPrepUpdate(query, [newnumber, date, seletedID], getKey=1) ``` Im not sure though if the way I am doing the arguments is correct. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [April 4, 2019, 10:13am UTC](https://forum.inductiveautomation.com/t/duplicate-a-table-row-that-is-linked-to-another-table-with-foriegn-keys/23834/6 "2019-04-04T10:13:21Z")
Close: ```auto Query = "INSERT INTO recipes (recipes_id, Description, DateCreated) SELECT ? As recipes_id, Description, ? As DateCreated FROM recipes WHERE recipes_id = ?" ``` Note the use of actual column names for your insert. ---
**Author:** ![bellm](https://avatars.discourse-cdn.com/v4/letter/b/b5e925/32.png) [@bellm](https://forum.inductiveautomation.com/u/bellm)\ **Post date:** [April 4, 2019, 10:33am UTC](https://forum.inductiveautomation.com/t/duplicate-a-table-row-that-is-linked-to-another-table-with-foriegn-keys/23834/7 "2019-04-04T10:33:48Z")
I see you used recipes\_id in the insert, that is the auto incrementing column so I would want to leave that one alone correct? I was referencing Number which is a user provided recipe number. ---
**Author:** ![bellm](https://avatars.discourse-cdn.com/v4/letter/b/b5e925/32.png) [@bellm](https://forum.inductiveautomation.com/u/bellm)\ **Post date:** [April 4, 2019, 12:28pm UTC](https://forum.inductiveautomation.com/t/duplicate-a-table-row-that-is-linked-to-another-table-with-foriegn-keys/23834/8 "2019-04-04T12:28:27Z")
I was able to get it all working so I tried to get fancy and I am having an issue. I want to copy the Description but append “- Copy” at the end but I am having an syntax problem. I’m sure its a simple fix. `query = "INSERT INTO recipes (Number, Description, DateCreated) SELECT ? AS Number, Description + '-Copy', ? AS DateCreated FROM recipes WHERE recipes_id = ?" NewID = system.db.runPrepUpdate(query, [number, date, id], database, getKey=1)` ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [April 4, 2019, 3:23pm UTC](https://forum.inductiveautomation.com/t/duplicate-a-table-row-that-is-linked-to-another-table-with-foriegn-keys/23834/9 "2019-04-04T15:23:28Z")
Use the concat() function in SQL with strings – many (most?) DBs don’t implement ‘+’ for strings.