EasyCFM.COM ColdFusion Forums / Oracle Related Issues / Insert Statment via Select

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Insert Statment via Select

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

dlackey
03-12-2008 @ 2:50 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 935
Joined: Jun 2007

This isn't specific to Oracle but I'm using an Oracle database so I'm posting this year, just in case Oracle has a feature designed just for this sort of thing...

So, I have a form where a user can create a new record based on an existing record.  The user select an existing record to copy and then provides a new name (form.name) so that the new record will be distinict from the record that is being copied and clicks submit.  Copying the record is easy enough with the following generic sql code.

insert into tablename (name, contact, phone, etc)
select name, contact, phone, etc)
where id = #form.id#

Now, what I dont know how to do is to insert the form.name inplace of the name that will come from the select statement.  I tried leaving the name out of the query statement but the database wont accept a null value for name.  My initial thought was that I would duplicate the row and then simply obtain the last inserted value and update the name with the form.name value.  By doing it this way, there IS a chance that two people could be duplicate records at the same time so the max id may not be good enough since both users could be grabbing the same max id and attempting to update the same record.  I guess I could query the database with the values retrieved from the initial query since I know that ID value and then use the results from that as part of my where clause and then grab the maxid from the expected two records returned?  

can you think of or provide a better way?  

Since this is Oracle, I do have a trigger and a sequence if helps you help me.  Smile

Cheers,
Chip

dlackey
03-12-2008 @ 3:15 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 935
Joined: Jun 2007

I'm not sure if there is a better way but this worked...

insert into tablename(ID, NAME, CONTACT)
SELECT id, '#form.name#', '#form.contact#'
FROM tablename
where id = #form.id#

Of course I'll go back and add the cfqueryparams.  I'm just showing the most simple form for clarity.

I hope this helps someone and if you have a better method, please share.


Cheers,
Chip


Website Designed and Developed by Pablo Varando.