0

If I have a table, called Places with several fields, and a primary key placeID, what if the best way to INSERT a new item into the table, such that placeID is autoincremented, but all the other fields are the same as the item with placeID= 001,say.

This is what I am doing

 SELECT* FROM Places WHERE placeID = 001 ... INSERT INTO Places VALUES(...should be values from previous select)

but I can't quite figure out how to do it.

Improve this question

2 Answers 2

Reset to default
4

You just have it backwards, and you need to explicitly name your columns, with every column except placeID in the SELECT list:

INSERT INTO places (col1, col2, col3, col4, every_column_except_placeID) SELECT col1, col2, col3, col4, every_column_except_placeID FROM Places WHERE placeID='001'

You should get a new row with placeID auto-incremented.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Nearly correct, but you need to either specify every column INCLUDING placeID OR specify the specific columns you will be selecting. Insert into places (col1, col2, col3, col4, everycolumn_except_palceID) (select col1, col2, col3, col4, everycolumn_except_palceID from places ...)
0

If it seems like a mess in one SQL statement, then I break it up and use more resources. For large networked applications that need to squeeze every drop of performance-- this is a poor solution.

However, most of the time for less demanding circumstances, a nice visually logical process is just fine.

I would select my information, and use an inner loop of a higher level language to drop the results into an insert statement. I am not penalized enough for making more inserts. On many shared hosts there is a limit to how many queries you can make in a time period, so maybe you need to build fewer, but bigger insert statements.

For this I would likely use PHP to handle my data and the SQL, I'm sure the following can be adapted to whatever language you would want to use.

<!php $placeid=001 $sql1="SELECT column,othercolumn,maybeasterisk FROM table1 WHERE PlaceID=$placeid"; $sql2="INSERT into table2 (column,othercolumn,thirdcolumn,whymore,so-on) VALUES ('$data1a','$data1b','$data1c','$data1d','$data1e')"; $sql3="INSERT into table2 (column,othercolumn,thirdcolumn,whymore,so-on) VALUES $bigstring"; $counter=0; $result1=mysql_query($sql1); while ($resultrow1=mysql_fetch_array($result1)) { $data1a=$resultrow1['1']; $data1b=$resultrow1['2']; $data1c=$resultrow1['3']; $data1d=$resultrow1['whymore']; $data1e=$resultrow1['so-on']; if ($abillioninsertsareokay='true') { mysql_query($sql2); } else { //a billion inserts isn't okay //build an array that we can use in a separate loop for ($i=0, $i<=5, $i++) { $sql3arraydata['$counter']['$i'] $counter++ } } $counter=0; if ($abillioninsertsareokay!='true') { foreach $sql3arraydata['$counter'] { if ($counter>0) { $bigstring.=', '; } $bigstring.="('$sql3arraydata['$counter'][1]','$sql3arraydata['$counter'][2]','$sql3arraydata['$counter'][3]','$sql3arraydata['$counter'][4]','$sql3arraydata['$counter'][5]')"; } mysql_query($sql3); //one huge insert statement } ?> It may be the wrong way to look at things, but sometimes you just have to crank out mediocre code that works to get the job done. Yes, that would be great if you had all the time in the world to make it super awesome, but if this is the kind of question you are asking-- probably not a mission critical system for a mass amount of people.
Improve this answer

Comments