duplicate a subset of records in a table

[Dale]

Hi guys,

Is there a way I can duplicate a subset of records within the same table
with all the fields values being identical except for one field. For
instance, if I had the following two records I'm interested in:

Field01 Field02 Field03
------- ------- -------
test1 value1 value2
test1 value3 value4

I'd like the following to be inserted in the same table

test2 value1 value2
test2 value3 value4

where only Field01's value has been changed. I'll be executing this SQL
programmatically so I can construct the SQL with the new Field01 value
on the fly.

I was hoping it'd be a statement something like:

INSERT INTO tablename (SELECT * FROM tablename WHERE FIELD01 =
[field01_value])

The WHERE clause will be a little more complicated than that but I'm
just using it as an example for now. I'll programmatically replace a
proper value for [field01_value] .... I guess my question is, can I
within the SELECT part replace FIELD01's value with some other value
which I will know at runtime. Can my SQL even work? Thanks.

I think I can see where it'll work if I put all (but the FIELD01 name)
the field names down instead of the '*'. The FIELD01 name I would just
put my constant value. I'm trying to avoid putting all the field names
down since my real table has many more fields that the 3 fields in my
example. Thanks for any suggestions.

 

[Kevin11]

Sounds like you need an append query. Create a regular
query for Field01, Field02, Field03, and and expression
column newField01

Set the Criterial for Field01 to equal what you are
copying (e.g. = test1). Set the newfield01 expression to
the replacement value (e.g. newfield01: "test2").

Change it to an Qppend query and map newfield01 to Field01
in your original table. Something like that should work
OK I think. Good Luck.

Kevin11

 

[Dale]

Thanks Kevin, I tried it, I was going to do something like that - it
lists all the fields though ... I thought maybe I could just use a "*"
to get all the fields.

Sounds like you need an append query. Create a regular
query for Field01, Field02, Field03, and and expression
column newField01

Set the Criterial for Field01 to equal what you are
copying (e.g. = test1). Set the newfield01 expression to
the replacement value (e.g. newfield01: "test2").

Change it to an Qppend query and map newfield01 to Field01
in your original table. Something like that should work
OK I think. Good Luck.

Kevin11

-----Original Message-----
Hi guys,

Is there a way I can duplicate a subset of records within

the same table

with all the fields values being identical except for

one field. For

instance, if I had the following two records I'm

interested in:

Field01 Field02 Field03
------- ------- -------
test1 value1 value2
test1 value3 value4

I'd like the following to be inserted in the same table

test2 value1 value2
test2 value3 value4

where only Field01's value has been changed. I'll be

executing this SQL

programmatically so I can construct the SQL with the new

Field01 value

on the fly.

I was hoping it'd be a statement something like:

INSERT INTO tablename (SELECT * FROM tablename WHERE

FIELD01 =

[field01_value])

The WHERE clause will be a little more complicated than

that but I'm

just using it as an example for now. I'll

programmatically replace a

proper value for [field01_value] .... I guess my question

is, can I

within the SELECT part replace FIELD01's value with some

other value

which I will know at runtime. Can my SQL even work?
Thanks.

I think I can see where it'll work if I put all (but the

FIELD01 name)

the field names down instead of the '*'. The FIELD01

name I would just

put my constant value. I'm trying to avoid putting all

the field names

down since my real table has many more fields that the 3

fields in my

example. Thanks for any suggestions.

.