duplicating\copying rows

i got a row with 10 fields that i want to duplicate...but i need to copy only 7 fields from the 10.
how i can do it?

i found a temo way now, i am getting the values with rs with one query and inserting those values with another.
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.

Jinesh KamdarCommented:
Include only those fields that u want to duplicate.

INSERT INTO tableA (col1, col2, ..... col7)
VALUES (rs.val1, rs.val2, ... rs.val7)
0
TextReportCommented:
INSERT INTO Table1 (Field4, Field5, Field6, Field7, Field8, Field9, Field10)
SELECT Field4, Field5, Field6, Field7, Field8, Field9, Field10
FROM Table1
WHERE Field1 = 1

The assumption here is that you have default values for Field1, Field2 and Field3.

Cheers, Andrew
0
Jinesh KamdarCommented:
TextReport suggested a very valid pre-req - The assumption here is that you have default values for Field1, Field2 and Field3.

To add to it, these fields may either be NULL or they may be NOT NULL and should have a default value.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

sasha85Author Commented:
just a minute...i forgot to say something...
al the duplication going on in the same table...

for example i got row with id=5
now i want to create row where id will be 6
and to copy 7 fields...

and i do not want to use only one query-with no rs
0
TextReportCommented:
I made the assumption that that rowid is an identity row and will increment itself.

The code below will add one to the ID field if youre not using a identity column.
Cheers, Andrew
INSERT INTO Table1 (ID, Field5, Field6, Field7, Field8, Field9, Field10)
SELECT ID+1, Field5, Field6, Field7, Field8, Field9, Field10
FROM Table1
WHERE ID = 5

Open in new window

0
sasha85Author Commented:
i just can use ID and ID+1 instead of rs("id")?:)
0
sasha85Author Commented:
i think i was right and there is not such option...but i am not sure...that is the error i get:
[MySQL][ODBC 3.51 Driver][mysqld-4.1.21-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ש','××S ×¢×£',DATE_ADD(LOCALTIME(),INTERVAL 120 minute),'12675645-1633' at line 1

0
TextReportCommented:
Can you show the code you are running top get this error.
Cheers, Andrew
0
sasha85Author Commented:
yes...as soon as i get back to that computer i will post it...
but you sure in your suggestion syntax?
i mean have you tested it before?

thank you once again:)
0
TextReportCommented:
Yes pretty much bog standard SQL, however, normally the ID field is an auto increment so you would not specify the field in either the fields list of teh SELECT.
Cheers, Andrew
0
Jinesh KamdarCommented:
If the ID is an auto-increment or PK field and if there are already records in the table having id value of 6 or more, then it won't work, though ur error seems to be different than that. Try this.
INSERT INTO Table1 (ID, Field5, Field6, Field7, Field8, Field9, Field10)
SELECT (SELECT MAX(ID) FROM Table1) + 1, Field5, Field6, Field7, Field8, Field9, Field10
FROM Table1
WHERE ID = 5;

Open in new window

0
sasha85Author Commented:
mysql="INSERT INTO Table1 (ID, Field5, Field6, Field7, Field8, Field9, Field10) SELECT (SELECT MAX(ID) FROM Table1) + 1, Field5, Field6, Field7, Field8, Field9, Field10 FROM Table1 WHERE ID = 5"
isn'  there a ) missing?

why id=5?
i never know what will be the id of the new record...
0
Jinesh KamdarCommented:
>> isn'  there a ) missing?

Nope.

>> why id=5?

If not this, then what is the filter condition that determines which records to be duplicated?
Or do u want to duplicate them all?
0
sasha85Author Commented:
sorry but i will tell you what i do not get here:
usually the syntax is:
mySQL="INSERT INTO table(columns names) VALUES (values that wil be unserted...)

your query got no" VALUES"
0
Jinesh KamdarCommented:
We cannot use the VALUES keyword when the INSERT command gets its values from an existing table.

1. INSERTing data from pre-defined values -
INSERT INTO table (id, name) VALUES (1, 'ABC');

2. INSERTing data from a table -
INSERT INTO table (id, name) SELECT id, name FROM table2;
0
sasha85Author Commented:
here is my original query after the you help:
mysql="INSERT INTO demo (newid,starttask,firstread,lastread,uname,tasks,ptime,sub,fromloc,toloc,customer,hlimit,carnum,status) SELECT (SELECT MAX(id) FROM demo) + 1, '" &starttask& "','" &firstread& "','" &lastread& "','" &uname& "','" &tasks& "','" &ptime& "','" &sub& "','" &fromloc& "','" &toloc& "','" &customer& "','" &hlimit& "','" &carnum& "',1  FROM demo WHERE id=" &pid

and there is a syntax error going on there
0
sasha85Author Commented:
you right...i was using pre definded vars...
0
sasha85Author Commented:
ok...but there is a number that i have to insert...1 to status
mysql="INSERT INTO demo (newid,starttask,firstread,lastread,uname,tasks,ptime,sub,fromloc,toloc,customer,hlimit,carnum,status) SELECT (SELECT MAX(ID) FROM demo) + 1,,starttask,firstread,lastread,uname,tasks,ptime,sub,fromloc,toloc,customer,hlimit,carnum,1  FROM demo WHERE id=" &pid


what is wrong with it?
0
sasha85Author Commented:
besides the double comma

mysql="INSERT INTO demo (newid,starttask,firstread,lastread,uname,tasks,ptime,sub,fromloc,toloc,customer,hlimit,carnum,status) SELECT (SELECT MAX(ID) FROM demo) + 1,starttask,firstread,lastread,uname,tasks,ptime,sub,fromloc,toloc,customer,hlimit,carnum,1  FROM demo WHERE id=" &pid
0
Jinesh KamdarCommented:
Try 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 & "'"

Open in new window

0
Jinesh KamdarCommented:
Reposting correction.
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

0
sasha85Author Commented:
i don't why, but when i use your code, the new record that have to be opened insert all the data to the next row...
that mean if whe now on row id=5 and there is 50 rows...then when we will use this query, there won;t be opened 51 row...instead of it the row with id 6 will receve the changes...
0
Jinesh KamdarCommented:
>> then when we will use this query, there won;t be opened 51 row...instead of it the row with id 6 will receve the changes...

I don't believe thats possible unless the - SELECT MAX(id) FROM demo - subquery itself fetches the value as 5. As an alternative, try this.
mysql="INSERT INTO demo(newid           ,starttask,firstread,lastread,uname,tasks,ptime,sub,fromloc,toloc,customer,hlimit,carnum,status) "
& "SELECT (SELECT COUNT(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:
that is not good cause the id's could be deleted...
0
TextReportCommented:
sasha85, I have said it in your other qustion and I will repeat it here as it is a fundamental issue and is causing all your problems here.

"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
0
sasha85Author Commented:
i am using auto inc..."newid" is like a serial number
0
sasha85Author Commented:
there is "id" and "newid"
0
sasha85Author Commented:
ALL i want is to copy some records fields into new record and place the new record last...as it usually being added.
why that is so big problem?
0
TextReportCommented:
Too many cooks I should think. You have a choice of 2 ways to do this, please see below.
You do not specify a value for NEWID in either of these examples.

I would suggest we concentrate in getting the INSERT creating the record for you. As stated you must have default values or the field must allow nulls if you haven't specified a value for the field.

Cheers, Andrew
Option 1: ID:20850451 First posted here by jinesh_kamdar
INSERT INTO tableA (col1, col2, ..... col7)
VALUES (rs.val1, rs.val2, ... rs.val7)
 
Option 2: ID:20850467 First posted here by TextReport
INSERT INTO Table1 (Field4, Field5, Field6, Field7, Field8, Field9, Field10)
SELECT Field4, Field5, Field6, Field7, Field8, Field9, Field10
FROM Table1
WHERE Field1 = 1

Open in new window

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
Jinesh KamdarCommented:
@sasha85: Can u just show us wat does this SQL fetch - SELECT MAX (id) FROM Table1
0
sasha85Author Commented:
i will explain:
when you open a new recored you don'tknow what auto id it will recieve cause may be some one else in the same moment opened a record too...

i need that in the id field(key) and in the newid field will be the same number...but because i can't know the number that the id field will recieve, i am telling the the newid field to be the maximum number of id+1
0
Jinesh KamdarCommented:
If u want them both to be the same, why don't u make them both as Auto-Inc ?
0
TextReportCommented:
You can only have 1 autoIncrement field in a table but I don't understand why you would have 2 ID fields 1 auto Invrement and 1 the MAX of AutoIncrement + 1 or 2 fields with the same value.
Cheers, Andrew
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.