Duplicate a table row that is linked to another table with foriegn keys

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:

Recipe Table:

Coats Table:

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.

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.

Thank you, how would I go about retrieving the recipe id during the first operation?

Typically using the getKey option to system.db.runPrepUpdate() or your DB’s equivalent.

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:

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.

Close:

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.

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.

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)

Use the concat() function in SQL with strings – many (most?) DBs don’t implement ‘+’ for strings.

2 Likes