sasha85
asked on
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)
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 & "'"
ASKER
on my query i pasted the id for the new record not specified...
as i understand the:
id = '" & pid & "'"
use only for the copy?
as i understand the:
id = '" & pid & "'"
use only for the copy?
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.
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.
ASKER
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,lastr
& "SELECT (SELECT MAX(ID) + 1 FROM demo),starttask,firstread,
like this:
mysql="INSERT INTO demo(newid ,starttask,firstread,lastr
& "SELECT (SELECT MAX(ID) + 1 FROM demo),starttask,firstread,
You cannot insert into the same table - what is the point?
ASKER
duplicating the record...
for example in row with id=5 there is 6 fields that i want to copy to a new record
for example in row with id=5 there is 6 fields that i want to copy to a new record
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
sorry but what was changed here?
You can see the changes I made if you compare my example against yours.
ASKER
u deleted the newid value and added , 1 AS Status
Among other things, that is correct.
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.
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 ASKER
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
"You appear to be trying to insert a value into your ID field"
where is that line wich do this?
where is that line wich do this?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Cheers, Andrew