Re: UPDATE ... SELECT FROM ... :)

2002-12-05 Thread Michael T. Babcock 
On Wed, Dec 04, 2002 at 04:32:35PM -0500, Michael T. Babcock wrote:
 information from a to b.  Any ideas for a one-shot SQL command to do this?
 I'm about to try selecting everything from b and the CommID from a as a 
 join into a temporary table, then REPLACE it back into b.
 
In case anyone wants to know how to do an UPDATE ... SELECT FROM ...,
I did the equivalent of:

BEGIN;
ALTER TABLE b ADD Col3 ...;

CREATE TABLE CopyCol3
  SELECT b.id, b.Col1, b.Col2, a.Col3 
FROM b 
   LEFT JOIN a 
  ON a.id = b.id;

REPLACE INTO b (Col1, Col2, Col3) 
  SELECT * 
FROM CopyCol3;

DROP TABLE CopyCol3;
ALTER TABLE a DROP Col3;
COMMIT;

This was to move the value Col3 from table a to b based on 'id'.

-- 
Michael T. Babcock
CTO, FibreSpeed Ltd. (Hosting, Security, Consultation, Database, etc)
http://www.fibrespeed.net/~mbabcock/

-
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 [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

UPDATE ... SELECT FROM ... :)

2002-12-04 Thread Michael T. Babcock 
I have a table a with:

ItemID int unsigned not null,
CommID int unsigned not null

I have a table b with:
ItemID int unsigned not null

I want to move CommID from table a to table b.  I've updated my schema 
to have CommID in b as well for now, and changed my code to insert the 
value into table b instead of a.  I now just need to transfer the existing 
information from a to b.  Any ideas for a one-shot SQL command to do this?
I'm about to try selecting everything from b and the CommID from a as a 
join into a temporary table, then REPLACE it back into b.

-- 
Michael T. Babcock
CTO, FibreSpeed Ltd. (Hosting, Security, Consultation, Database, etc)
http://www.fibrespeed.net/~mbabcock/

-
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 [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php