mySQL - duplicate a row

[bsouthuk]

Hi there

I wonder is someone is able to help me...

I have created a mySQL table with around 50 columns for my Livecode application. There are instances where I would like to duplicate a row in the same table. Would anyone happen to the code I could use for this?

Thanks

Daniel

[sturgis]

In simplest form, all data from one row of a table into a new row of a table should be something like this

insert into settings select * from settings where hostname="foo"

where settings is the table name and the where clause matches the unique id (or whatever) of the row you wish to duplicate.

sql isn't my main strength though so test carefully.

[bsouthuk]

Thanks for that, I feel as though I am close with the following:

Code: Select all

global gConnectionID

//set up the connection parameters - edit these to suit your database

put "xxx" into tDatabaseAddress
put "xxx" into tDatabaseName
put "xxx" into tDatabaseUser
put "xxx" into tDatabasePassword

//connect to the database
put revOpenDatabase("MySQL", tDatabaseAddress, tDatabaseName, tDatabaseUser, tDatabasePassword) into tResult

//check if it worked and display an error message if it didn't & set the connection ID global
if tResult is a number then
put tResult into gConnectionID
else
put empty into gConnectionID
answer error "Unable to connect to the database:" & cr & tResult
exit to top
end if


//////FIELDS TO UPLOAD TO DATABASE VARIABLES
put "QGNew" into tTableName


put "INSERT INTO " & tTableName & " FROM QGNew WHERE ID ='" & (field "ID" of card 38) into tSQL


revExecuteSQL gConnectionID, tSQL
answer the result



//check the result and display the data or an error message
if the result is a number then
   //put tData into field "Reference"card 2
//answer "record added"
else
   answer error "There was a problem adding the User to the database:" & cr & the result
end if

global gConnectionID

-- if we have a connection, close it and clear the global connection ID
if gConnectionID is a number then
   put tData into field "MobileID"card 6
revCloseDatabase gConnectionID
put empty into gConnectionID
end if

However, I am getting an error message saying an error with my syntax...

[sturgis]

EDIT: Just noticed also that you forgot the closing ' (single quote) around your where value. However, if your ID is a primary key, it should probably be an integer (if you're auto incrementing) so no ' ' are needed around the value anyway. Will change the posted string to fix that (assuming that you WANT the ' around the value)

You forgot the select keyword

"INSERT INTO " & tTableName & " SELECT * FROM QGNew WHERE ID ='" & (field "ID" of card 38) & "'" --<-- added closing '

So, from the way this appears you have several tables with the same format (hence using tTableName variable to supply the place to put the copy) but all original data will always come from table QGNew? (Just curious since QGNew seems to be named as if it is a recipient rather than the source)

If its always from and to the same table, you can adjust things so that you use the variable for both.

Also, to make this sort of thing easier you might look at the merge() function.

It allows you do do stuff like this.
put "fred" into tTablename
put 3 into tId
put merge("SELECT FROM [[tTablename]] where ID=[[tId]]") into tSql

Makes it MUCH easier to build query strings since You don't have to worry about all the " " & && stuff
It just "merges" in the variable values for any placeholders in [[ ]] In fact, you can even have [[quote]] so that a quote will appear in the string wherever you need. (I usually put quote into a var named q and then merge("[[q]].....")

[sturgis]

Just did some more reading. If you are duplicating the row and it does have a primary key there can be problems. One method listed is to use a temporary table to handle the transfer, though I'm thinking if you select all columns EXCEPT the ID it might still work similarly to your current effort. Did I mention that sql is not my main forte?

You can find more info here: http://www.av8n.com/computer/htm/clone-sql-record.htm

[bsouthuk]

Hey that's great thank you very much, the only error I get now is the duplicate entry as each record has an ID, therefore the 'ID' column is Primary set as AUTO_INCREMENT.

Instead of temp tables is there an easier way where I could use:

Code: Select all

put "INSERT INTO " & tTableName & " SELECT * FROM QGNew WHERE ID ='" & (field "ID" of card 38) & "'" into tSQL

But add some code that excludes 'ID' column?

[sturgis]

Probably the best way is to manually list the columns to select (yes it can be a pain!) If you always have the same columns that you're working with, you can save the column list into a property so that you can use the property name to populate things. Then you can just exclude the ID column from the list for your select, and when you do the insert do the same thing. insert into (the list of columns) where id = whatever.

At least I think it will work.

so for example if you had your list of columns (in the correct order?) in a variable called tColumns you should be able to do something like..

put merge("INSERT into whatevertable SELECT [[tColumns]] from whatevertable where ID=yourid") into tsql


Not sure if you have to specify column names on the insert portion to get this to work. HMm yeah after reading a bit more you might use your column names for the insert portion also.
Something that might work. Again assuming variable tColumns contains your list of column names in the form of "col1,col2,col3,col4" but without the ID column...

put merge("INSERT into whatevertable ([[tColumns]]) values (SELECT [[tColumns]] from whatever table where id = yourId)" into tSql
(might not need the values keyword. If I get time and this won't work I may set up a test db later today to see if I can find the right recipe. )

[sturgis]

Ok, got one that seems to work.

I set up a small database with 5 columns, 1 is id, then there are col2,col3,col4,col5

I placed "col2,col3,col4,col5" into variable named tColumns
I placed the table name "myTable" into tTable
I manually set tId to the unique key of the row to duplicate

Then set up the tSql string like this:

Code: Select all

   put merge("insert into [[tTable]] ([[tColumns]]) SELECT [[tColumns]] from [[tTable]] where ID=[[tId]]") into tSql

Then used revexecutesql to insert the row

Code: Select all

   revexecutesql sDbId,tSql

and it worked fine. Of course this is on sqlite only, chances are there will be differences in a mysql version but I can't really test on mysql right now. Something similar to this will PROBABLY work though. If I get an easy way to test on mySql I'll do so and try to figure out the exact syntax. (try this first though, maybe you'll get lucky and it will function unchanged)

[bsouthuk]

r

[bsouthuk]

Top man! Thats great, I have:

put "QGNew" into tTable
put field "ID" into tId
put "Date, Time, StepHandset, StepTariff, StepAddon, DealerID, Agent, UserID, Network, Distributor, Customer, CustomerID, ContactName, Order2, TotalHandsetCost, TotalAccessoriesCost, GrandTotalCost, TotalHandsetBonus, TotalHandsetDevices, TotalBlackBerryDevices, TotalDataOnlyDevices, Connections, TeamNameID, TeamName, Status, HandsetList, Package, TotalVoiceQTY, TotalDataQTY, TotalTariffQTY, TotalTariffCost, UpfrontCommission2, RevenueAdvance2, UpfrontCommissionTotal2, Rev12, Rev24, Rev36, TotaliPhoneDevices, Rev48, GuaranteedBonus, GuaranteedBonusQTY, GuaranteedBonusTotal, VolumeBonus, VolumeBonusQTY, VolumeBonusTotal, OtherBonus, OtherBonusQTY, OtherBonusTotal, EstimatedDCP, EstimatedDCPQty, EstimatedDCPTotal, ChargeCustomer, HardwareRentalCharge, CashBack, HardwareFund, BuyOutCost, LineRentalCredit, CreditLabel, LineRentalCreditAverage, ContractLength, NewOrResign" into tColumns

put merge("insert into [[tTable]] ([[tColumns]]) SELECT [[tColumns]] from [[tTable]] where ID=[[tId]]") into tSql


revExecuteSQL gConnectionID, tSQL

Works perfectly.

I have another question know that may complicate it. As you can see from the tCollumns list, I have date and time. I need this to be the current date and time instead of the cloned records. My question therefore is, say if 2 of the collumns need to be changed?

[sturgis]

Ok, my guess would be this..

Instead if using your column names in your list you can "select" the current date and time
Something like

"col1,col2,date() as Date,now() as Time,anotherCol,yetAnotherColumn....

For date it might be today() not sure, yet another thing to look up.

Ah k. for date most likely use currdate() and for time use now()

(this would be on the select side. So you'll need 2 sets of column names, one for the select that will set up to "fake" columns for the date and time with current date and time, and the other that specifies columns to fill.

Again, not tested, no clue if such a beast will be easy to make work. Good luck!

[sturgis]

Just had another thought.

You might be able to set up a trigger on the database so that when a new record is inserted, the current date and time is automatically placed into those fields. (google mySql trigger)

If thats the case, you could just remove those 2 fields from your list and it will all happen almost magically.

[bsouthuk]

Thats great, thank you again for your help.

I have opted for the following though dont quite know how watertight this is:

Code: Select all

put "SELECT ID FROM QGNew ORDER BY ID DESC LIMIT 1" into tSQL


put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData
put revDataFromQuery(tab, cr, gConnectionID, tSQL) into field "ID2"

put date2mysql (the system date) into tDate
put the time into tTime

put "UPDATE QGNew SET Status=" & "'" & ("Incomplete") & "'" & "WHERE ID=" & "'" & (tData) & "'" into tSQL2
put "UPDATE QGNew SET Date=" & "'" & (tDate) & "'" & "WHERE ID=" & "'" & (tData) & "'" into tSQL3
put "UPDATE QGNew SET Time=" & "'" & (tTime) & "'" & "WHERE ID=" & "'" & (tData) & "'" into tSQL4

revExecuteSQL gConnectionID, tSQL2
revExecuteSQL gConnectionID, tSQL3
revExecuteSQL gConnectionID, tSQL4

We'll find out im sure!

[Klaus]

Hi Daniel,

looks OK, but we can get the db calls down to 2 instead of the 5 calls you are currently making
And please create a function that does the QUOTING for you, see below! Will mkae the code more readable!

Code: Select all

...
put "SELECT ID FROM QGNew ORDER BY ID DESC LIMIT 1" into tSQL

put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData
put tData into fld "ID2"

put date2mysql (the system date) into tDate
put the time into tTime

## You can update more than one db field at once!
put "UPDATE QGNew SET Status=" & q2("Incomplete") & ",Date=" & q2(tDate) & ",Time=" q2(tTime) && "WHERE ID=" & q2(tData) into tSQL
revExecuteSQL gConnectionID, tSQL
...
## QUOTE
function q tString
    return QUOTE & tString & QUOTE
end q

## Single QUOTE
function q2 tString
    return "'" & tString & "'"
end q2

Refresh you SQL knowledge here:
http://www.w3schools.com/sql/default.asp

Best

Klaus