mysql: how create a temp table as a copy of a existing table?

[dev]

how create a temp table as a copy of a existing table and then update1 field
and insert the hole temp table back in the existing table?
please any help?

if i have 10 fields in 1 record and about 100 records and a field.status=1
in a existing_table and
i want to create a temp_table with all the recordse and values of the
existing_table and then update the field.status to 2 and insert in 1 query
the temp_table in the existing_table

Best regards,
m

[Michael Austin]

dev wrote:

how create a temp table as a copy of a existing table and then update1 field
and insert the hole temp table back in the existing table?
please any help?

if i have 10 fields in 1 record and about 100 records and a field.status=1
in a existing_table and
i want to create a temp_table with all the recordse and values of the
existing_table and then update the field.status to 2 and insert in 1 query
the temp_table in the existing_table

Best regards,
m

Apparently you have never studied anything about [relational] databases and how
they function in a multi-user environment. You obviously have a problem that
needs to be solved, but this isn't the way to do it. Explain the business
challenge that led you to believe that this was the solution.

What's wrong with:

Update existing_table set field_status = 2 where field_a = 'somevalue';

or to update all records and set the status to 2:::

update existing_table set field_status=2;

Again, what problem are you trying to solve?

--
Michael Austin.
Consultant - Available.
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)

[me]

Michael Austin wrote:

Apparently you have never studied anything about [relational] databases and
how

they function in a multi-user environment. You obviously have a problem that needs to be solved, but this isn't the way to do it. Explain the business
challenge that led you to believe that this was the solution.

What's wrong with:

Update existing_table set field_status = 2 where field_a = 'somevalue';

or to update all records and set the status to 2:::

update existing_table set field_status=2;

Again, what problem are you trying to solve?

--
Michael Austin.
Consultant - Available.
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)

Hey Michael,

wel the problem is not so easy i must adjust (alter) a existing table
structuur and php.
First i need the old value's! because i want to insert a PREVIEW function in
the content-layer tool
so i need a copy of the existing table (also relative on demand by user
input) and set the status to 2 so that i wil use for the preview
functionalty without change the old value's in the table..

so i hope this sort explaination is enough?

anyhelp on how to duplicate a table in a temp table with all the indexes
correct?

Cheers,

[Michael Austin]

me wrote:

Michael Austin wrote:

Apparently you have never studied anything about [relational] databases and
how

they function in a multi-user environment. You obviously have a problem

that

needs to be solved, but this isn't the way to do it. Explain the business
challenge that led you to believe that this was the solution.

What's wrong with:

Update existing_table set field_status = 2 where field_a = 'somevalue';

or to update all records and set the status to 2:::

update existing_table set field_status=2;

Again, what problem are you trying to solve?

--
Michael Austin.
Consultant - Available.
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)

Hey Michael,

wel the problem is not so easy i must adjust (alter) a existing table
structuur and php.
First i need the old value's! because i want to insert a PREVIEW function in
the content-layer tool
so i need a copy of the existing table (also relative on demand by user
input) and set the status to 2 so that i wil use for the preview
functionalty without change the old value's in the table..

so i hope this sort explaination is enough?

anyhelp on how to duplicate a table in a temp table with all the indexes
correct?

Cheers,

Again, you are going to have a REAL hard problem syncronizing the tables with
this approach in a multi-user environment, especially if you try to have more
than 1 user "replacing" the "existing_table" at the same time- who wins? the
last person to overwrite it... and the changes from the other users will be lost.

In a WEB-based environment you CANNOT ever assume that this will/can never
happen, because it will and if this data is imperative to your process, you are
now hosed. What happens if your user "aborts" and goes home for the night? now
you have abandoned/orphaned temp tables that you must clean up.

Bad plan Stan!

Read ONLY the data "to be changed" into a session variable and pass it along
until you get to the end... something like _SESSION['ROW1_COL1']="abc". and then
update only the row necessary.

Sounds like you could use a (DBA) consultant to help with this... :)

Beware of programmers posing as DBA's.
Beware of programmers with screw-drivers.
Beware of programmers in the computer room.

--
Michael Austin.
Consultant - Available.
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)

[me]

Michael wrote:
"Michael Austin" <ma*****@firstdbasource.com> wrote in message
news:A%******************@newssvr23.news.prodigy.c om...

me wrote:

Michael Austin wrote:

Apparently you have never studied anything about [relational] databases and how

they function in a multi-user environment. You obviously have a problem
that

needs to be solved, but this isn't the way to do it. Explain the businesschallenge that led you to believe that this was the solution.

What's wrong with:

Update existing_table set field_status = 2 where field_a = 'somevalue';

or to update all records and set the status to 2:::

update existing_table set field_status=2;

Again, what problem are you trying to solve?

--
Michael Austin.
Consultant - Available.
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)

Hey Michael,

wel the problem is not so easy i must adjust (alter) a existing table
structuur and php.
First i need the old value's! because i want to insert a PREVIEW function in the content-layer tool
so i need a copy of the existing table (also relative on demand by user
input) and set the status to 2 so that i wil use for the preview
functionalty without change the old value's in the table..

so i hope this sort explaination is enough?

anyhelp on how to duplicate a table in a temp table with all the indexes
correct?

Cheers,

Again, you are going to have a REAL hard problem syncronizing the tables

with this approach in a multi-user environment, especially if you try to have more than 1 user "replacing" the "existing_table" at the same time- who wins? the last person to overwrite it... and the changes from the other users will be lost.
In a WEB-based environment you CANNOT ever assume that this will/can never
happen, because it will and if this data is imperative to your process, you are now hosed. What happens if your user "aborts" and goes home for the night? now you have abandoned/orphaned temp tables that you must clean up.

Bad plan Stan!

Read ONLY the data "to be changed" into a session variable and pass it along until you get to the end... something like _SESSION['ROW1_COL1']="abc". and then update only the row necessary.

Sounds like you could use a (DBA) consultant to help with this... :)

Beware of programmers posing as DBA's.
Beware of programmers with screw-drivers.
Beware of programmers in the computer room.

--
Michael Austin.
Consultant - Available.
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)

Hey,

wel it is only for 1 user at once! admin user..
and sessions are not logic.. it has a whole content layout of some products
i must display..
so any help on the mysql table?

Cheers and thank for your help anyway..

[Virgil Green]

"dev" <NO***********************@hotmail.com> wrote in message
news:40*********************@news.xs4all.nl...

how create a temp table as a copy of a existing table and then update1 field and insert the hole temp table back in the existing table?
please any help?

if i have 10 fields in 1 record and about 100 records and a field.status=1
in a existing_table and
i want to create a temp_table with all the recordse and values of the
existing_table and then update the field.status to 2 and insert in 1 query the temp_table in the existing_table

Try this, pulled straight from the MySQL on-line manual.
=======
As of MySQL 3.23, you can create one table from another by adding a SELECT
statement at the end of the CREATE TABLE statement:
CREATE TABLE new_tbl SELECT * FROM orig_tbl;

MySQL will create new column for all elements in the SELECT.
=======

I haven't tested it myself, but it looks like what you need, followed by the
necessary UPDATE statements. I don't think it will build your indexes and
such, but if you are content with copying the whole table, you probably
don't need supporting indices.

If you're using MySQL 4.1, there is a 'CREATE TABLE new_tble LIKE old_tbl'
syntax as well. That, I think retains the characteristics of the table,
including indices.

Again, haven't tried these myself. Just exploring along with you.

- Virgil

[Timothy Madden]

On Fri, 23 Jul 2004 19:02:34 GMT, Virgil Green wrote:

"dev" <NO***********************@hotmail.com> wrote in message
news:40*********************@news.xs4all.nl...

how create a temp table as a copy of a existing table and then update1 field

and insert the hole temp table back in the existing table?
please any help?

<snip>
Try this, pulled straight from the MySQL on-line manual.
=======
As of MySQL 3.23, you can create one table from another by adding a SELECT
statement at the end of the CREATE TABLE statement:
CREATE TABLE new_tbl SELECT * FROM orig_tbl;

MySQL will create new column for all elements in the SELECT.
======= <snip> - Virgil

But belive the other fellow here: the point is you don't need to do that;
it is a *bad* idea.
--
Timothy Madden
Romania
------------------------------------
And I don't wanna miss a thing