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]

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

dev wrote:
[color=blue]
> 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
>
>[/color]

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]

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

Michael Austin wrote:

Apparently you have never studied anything about [relational] databases and
how[color=blue]
> they function in a multi-user environment. You obviously have a problem[/color]
that[color=blue]
> 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
> :)[/color]

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]

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

me wrote:[color=blue]
> Michael Austin wrote:
>
> Apparently you have never studied anything about [relational] databases and
> how
>[color=green]
>>they function in a multi-user environment. You obviously have a problem[/color]
>
> that
>[color=green]
>>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
>>:)[/color]
>
>
> 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,
>
>[/color]

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]

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

Michael wrote:
"Michael Austin" <maustin@firstd basource.com> wrote in message
news:A%ULc.1830 8$hH6.7920@news svr23.news.prod igy.com...[color=blue]
> me wrote:[color=green]
> > Michael Austin wrote:
> >
> > Apparently you have never studied anything about [relational] databases[/color][/color]
and[color=blue][color=green]
> > how
> >[color=darkred]
> >>they function in a multi-user environment. You obviously have a problem[/color]
> >
> > that
> >[color=darkred]
> >>needs to be solved, but this isn't the way to do it. Explain the[/color][/color][/color]
business[color=blue][color=green][color=darkred]
> >>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
> >>:)[/color]
> >
> >
> > 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[/color][/color]
function in[color=blue][color=green]
> > 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,
> >
> >[/color]
>
> Again, you are going to have a REAL hard problem syncronizing the tables[/color]
with[color=blue]
> this approach in a multi-user environment, especially if you try to have[/color]
more[color=blue]
> than 1 user "replacing" the "existing_table " at the same time- who wins?[/color]
the[color=blue]
> last person to overwrite it... and the changes from the other users will[/color]
be lost.[color=blue]
>
> 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,[/color]
you are[color=blue]
> now hosed. What happens if your user "aborts" and goes home for the night?[/color]
now[color=blue]
> 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[/color]
along[color=blue]
> until you get to the end... something like _SESSION['ROW1_COL1']="abc".[/color]
and then[color=blue]
> 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
> :)[/color]




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]

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

"dev" <NOSPAMDONALDDU CKVOIDEMAIL@hot mail.com> wrote in message
news:40ffffca$0 $563$e4fe514c@n ews.xs4all.nl.. .[color=blue]
> how create a temp table as a copy of a existing table and then update1[/color]
field[color=blue]
> 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[/color]
query[color=blue]
> the temp_table in the existing_table
>[/color]

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]

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

On Fri, 23 Jul 2004 19:02:34 GMT, Virgil Green wrote:
[color=blue]
> "dev" <NOSPAMDONALDDU CKVOIDEMAIL@hot mail.com> wrote in message
> news:40ffffca$0 $563$e4fe514c@n ews.xs4all.nl.. .[color=green]
>> how create a temp table as a copy of a existing table and then update1[/color]
> field[color=green]
>> and insert the hole temp table back in the existing table?
>> please any help?[/color][/color]
<snip>[color=blue]
>
> 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.
> =======[/color]
<snip>[color=blue]
> - Virgil[/color]

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