Copying data from and updating same table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
SFADuncan
Forum Newbie
Posts: 23
Joined: Mon Aug 11, 2003 5:06 pm
Location: uk

Copying data from and updating same table

Post by SFADuncan »

How do I write the following query?

I wish to :

copy the fields (ww.pg1, ww.pg2, ww.pg3) appearing in table ww

where

ww.bizname="Client_01"

and use this data to update a different row in the same table

where

ww.directory=$directory

Hope this make sense! (sorry formatting and working out the syntax in PHP queries ties me in knots!)

Simon
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

moving to Databases
Grim...
DevNet Resident
Posts: 1445
Joined: Tue May 18, 2004 5:32 am
Location: London, UK

Post by Grim... »

I'd like to know how to do something simular.

Basically, I think we both want to copy a row back into the same table. At the moment I use a SELECT query to get all the information and then a seperate INSERT query to add the new row(s).

There's got to be an easier way, right?
redmonkey
Forum Regular
Posts: 836
Joined: Thu Dec 18, 2003 3:58 pm

Post by redmonkey »

Not 100% sure but I think that would require a sub-select, something which (if a remember correctly) is not available in MySQL. Which database are you using?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

redmonkey wrote:something which (if a remember correctly) is not available in MySQL
IIRC, MySQL 5.0 has sub-selects.. :)
redmonkey
Forum Regular
Posts: 836
Joined: Thu Dec 18, 2003 3:58 pm

Post by redmonkey »

feyd wrote:
redmonkey wrote:something which (if a remember correctly) is not available in MySQL
IIRC, MySQL 5.0 has sub-selects.. :)
I wasn't taking into account 'alpha' versions. Considering the production release is only v4.0 I think you are in for a long wait for sub-selects in MySQL within a production environment. :wink:
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

all to true.. :( :D
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Post by liljester »

mysql 4 supports Insert ... Select Statements

INSERT (field1, field1)
SELECT field1, field2 WHERE something = true
Post Reply