-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Benjamin, et al --
...and then Benjamin Pflugmann said...
%
% Hi.
Hi again!
%
% On Fri 2002-12-27 at 20:37:58 -0500, [EMAIL PROTECTED] wrote:
% > Hi, all --
% >
% > What's the best way to change your database schema around if you have
% > data in it? How do you know when to simply ALTER TABLE and when to dump
% > the whole thing (perhaps doing specific SELECTs to spit out the data
% > you'd like to have in the end to be processed by some script that will
% > make the new table structure)?
%
% Well, I guess it depends. I would start with making a backup. :-)
Of course :-)
%
% Personally, I prefer to change the database with ALTER TABLE and
% UPDATEs, because I can get immediate feedback and I am comfortable
Hmmm... OK.
% enough with writing such SQL queries from head. Do not forget to save
% the SQL commands in a file in case you have to start from scratch
% (backup).
Makes sense. My inclination is to script it anyway, though for a real
one-time change maybe not. Maybe we'd want to apply the changes to a
restored copy from before tonight's new backup, for instance...
%
% I suppose if you are quite comfortable with e.g. Perl and/or the
% changes are going to be complex, writing a script is the better idea,
I don't know how complex things have to be before they're complex enough
for a script, but it sounds like it's possible. OK; so maybe one needs a
script. But, meanwhile, you can do this just within the database? Wow.
% because it is more forgiving if you make errors. OTOH, you have a
% bigger overhead, depending on how well you know your scripting
% language.
Fairly well -- and I could always stand to learn more :-)
%
% > I don't have a good example, but suppose I have a few tables with some
% > fields each and I realize that I should make a new table with data from
% > some columns of some tables. What to do?
%
% If you choose to go the SQL route, have a look at TEMPORARY TABLES,
% CREATE TABLE ... SELECT and INSERT ... SELECT. Don't forget that you
You mean a CREATE TABLE command that has SELECT as part of it, and an
INSERT command that has SELECT as part of it? Actually I wanted to know
how to do the latter but didn't think it could be done! Briefly:
create table ccards
(
# ID number
id smallint not null default 0 auto_increment primary key ,
...
hash tinyblob # hash of the card: have we seen this one before?
) ;
create table clientcards
(
# ID number
id smallint not null default 0 auto_increment primary key ,
client smallint , # references client.id
card smallint , # references ccards.id (but must be disconnected)
type smallint , # references ccardtypes.id ### need this here?
hash tinyblob # references ccards.hash (but must be disconnected)
) ;
...
insert into ccards (type,name,number,expdate) values
( '1' , 'david thorburn-gundlach' , '1234 5678 9abc def0' , '2003-06-00') ;
update ccards set hash = md5(number) where id = last_insert_id() ;
insert into clientcards values
( '' , '1' , '1' , '1' ,
select ccards.hash where ccards.id = last_insert_id() ) ;
Everything up to the clientcards insert works just fine. How can I
insert into clientcards the info including the hash from ccards? [I'm
just doing this for the testing phase, since not only will the ccards
table be locked away in a real database but I'll be inserting the values
one at a time from my front end where I'll calculate the md5 sum myself,
but it works as an example.]
% can test with the SELECT and only add CREATE TABLE / INSERT part at
% the end. And LIMIT is your friend.
OK :-)
%
% HTH,
%
% Benjamin.
%
% --
% [EMAIL PROTECTED]
Thanks & HAND & Happy Holidays
:-D
- --
David T-G * There is too much animal courage in
(play) [EMAIL PROTECTED] * society and not sufficient moral courage.
(work) [EMAIL PROTECTED] -- Mary Baker Eddy, "Science and Health"
http://www.justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (FreeBSD)
iD8DBQE+DXd5Gb7uCXufRwARAmtVAJ4i8dpmQz8yd/okzgTLdwXLn1HyiwCggIts
AOGgeFdZYnxRWLmgF6FDmVE=
=JheJ
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
