sql insert

how i make sure that a query will open new record -on the last place and not after the selected?

for example if i will use this record and pid=5
the records will set on the next record (6)and not on the last one (can be 232)
mysql="INSERT INTO demo(newid           ,starttask,firstread,lastread,uname,tasks,ptime,sub,fromloc,toloc,customer,hlimit,carnum,status) "
& "SELECT (SELECT MAX(ID) + 1 FROM demo),starttask,firstread,lastread,uname,tasks,ptime,sub,fromloc,toloc,customer,hlimit,carnum,1 FROM demo WHERE id = '" & pid & "'"

Open in new window

sasha85Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TextReportCommented:
In your table ID should be an Auto Increment field, you then do not specify the value for ID when you insert and the value will be automatically allocated.

Cheers, Andrew
0
sasha85Author Commented:
on my query i pasted the id for the new record not specified...
as i understand the:
id = '" & pid & "'"
use only for the copy?
0
BadotzCommented:
You cannot use

WHERE id = '" & pid & "'"

in an INSERT query - there is no value (yet) for the ID column. That is where the AutoNumber column does its magic.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

sasha85Author Commented:
ye...but when you inserting a new record and the values for that record are being copied...
like this:
mysql="INSERT INTO demo(newid           ,starttask,firstread,lastread,uname,tasks,ptime,sub,fromloc,toloc,customer,hlimit,carnum,status) "
& "SELECT (SELECT MAX(ID) + 1 FROM demo),starttask,firstread,lastread,uname,tasks,ptime,sub,fromloc,toloc,customer,hlimit,carnum,1 FROM demo WHERE id = '" & pid & "'"
0
BadotzCommented:
You cannot insert into the same table - what is the point?

0
sasha85Author Commented:
duplicating the record...
for example in row with id=5 there is 6 fields that i want to copy to a new record
0
BadotzCommented:
Try this:

mysql = _
"INSERT INTO demo " & _
"(newid, starttask, firstread, lastread, uname, tasks, ptime, " & _
"sub, fromloc, toloc, customer, hlimit, carnum, status ) " & _
"SELECT starttask, firstread, lastread, uname, tasks, ptime, " & _
"sub, fromloc, toloc, customer, hlimit, carnum, 1 AS Status " & _
"FROM demo " & _
"WHERE id = '" & pid & "'";
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sasha85Author Commented:
sorry but what was changed here?
0
BadotzCommented:
You can see the changes I made if you compare my example against yours.
0
sasha85Author Commented:
u deleted the newid value and added , 1 AS Status
0
BadotzCommented:
Among other things, that is correct.
0
TextReportCommented:
sasha85 please note that id is a number field so you do not need the ' around it.
Cheers, Andrew

PS I will raise this again it is very bad design to control the ID the way you are trying to do this. use the Auto Increment feature for the ID field.
INSERT INTO demo(newid, starttask, firstread, lastread, uname, tasks, ptime, sub, fromloc, toloc, customer, hlimit, carnum, status)
SELECT (SELECT MAX(ID) + 1 FROM Demo), starttask, firstread, lastread, uname, tasks, ptime,sub,fromloc,toloc,customer,hlimit,carnum,1
FROM demo 
WHERE id = " & pid 

Open in new window

0
sasha85Author Commented:
my question again:
WHERE id = " & pid
that is tha part that orders to save the record in the record where the id column have the variable pid?

cause if it is so its not good...i need that inserted last...

and i am using id auto inc...why do you thonk i don't?
0
TextReportCommented:
"and i am using id auto inc...why do you thonk i don't?" You appear to be trying to insert a value into your ID field, I am assuming the NEWID field is the auto increment field, therefore why do you need to do the MAX(ID)+1?

Cheers, Andrew
mysql = _
"INSERT INTO demo(newid, starttask, firstread, lastread, uname, tasks, ptime, sub, fromloc, toloc, customer, hlimit, carnum, status)" & vbcrlf & _
"SELECT (SELECT MAX(ID) + 1 FROM Demo), starttask, firstread, lastread, uname, tasks, ptime,sub,fromloc,toloc,customer,hlimit,carnum,1" & vbcrlf & _
"FROM demo " & vbcrlf & _
"WHERE id = " & pid & vbcrlf

Open in new window

0
sasha85Author Commented:
god no...i don't want to change the id field...the newid field is not auto inc -it is a serial number

for example there can be 4 records with the same serial number....the sireal number of all 4 of them will be the id field number of the first record
0
sasha85Author Commented:
"You appear to be trying to insert a value into your ID field"
where is that line wich do this?
0
TextReportCommented:
So is NEWID one of the fields you are copying from the original record? If so then the code below should be OK
Cheers, Andrew
mysql = _
"INSERT INTO demo(newid, starttask, firstread, lastread, uname, tasks, ptime, sub, fromloc, toloc, customer, hlimit, carnum, status)" & vbcrlf & _
"SELECT NewID, starttask, firstread, lastread, uname, tasks, ptime,sub,fromloc,toloc,customer,hlimit,carnum,1" & vbcrlf & _
"FROM demo " & vbcrlf & _
"WHERE id = " & pid & vbcrlf

Open in new window

0
TextReportCommented:
"You appear to be trying to insert a value into your ID field"
where is that line wich do this?

You have already explained that the NEWID is NOT an Auto Increment field so this is not the case.

Cheers, Andrew
0
thecodistCommented:
See if this is what you are looking for:

mysql="INSERT INTO demo(newid,starttask,firstread,lastread,uname,tasks,ptime,sub,fromloc,toloc,customer,hlimit,carnum,status) "
& "SELECT a.ID + 1,b.starttask,b.firstread,b.lastread,b.uname,b.tasks,b.ptime,b.sub,b.fromloc,b.toloc,b.customer,b.hlimit,b.carnum,1 FROM demo As a, demo As b WHERE a.id = (Select Max(id) From demo) And b.id = '" & pid & "'"
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.