Thanks for the input Jochem. I am testing the code using the MySQL Query Browser 1.1.20 and putting the script in a single Query window over multiple lines.
The first line create temporary table Ttable1 (select * from masterlist where ref='ABCDE'); works fine (well I don't get any errors). The problem is that when it processes the second line update Ttable1 set ref='SMI0C001'; it tells me that Ttable1 doesn't exist. I thought that temporary files exist for the session? Or am I testing the functionality the wrong way? Kerry -----Original Message----- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: 17 January 2008 19:34 To: MySql Subject: Re: creating temp file, modifying data and putting into other table On Jan 17, 2008 2:22 PM, Kerry Frater wrote: > Can someone please advise. I am looking to create a "multiuser" friendly way > of getting a subset number of rows from a table into another whilst making a > modification. > create temporary table Ttable1 (select * from masterlist where ref='ABCDE'); > update Ttable1 set ref='SMI0C001'; > insert into sublist select * from Ttable1; > drop Ttable1; How about: INSERT INTO sublist (ref, field1, field2, field3) SELECT 'SMI0C001' , field1 , field2 , field3 FROM masterlist WHERE ref='ABCDE' ; Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]