Page 1 of 1

Copying data from and updating same table

Posted: Tue Jul 13, 2004 4:06 pm
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

Posted: Tue Jul 13, 2004 4:08 pm
by feyd
moving to Databases

Posted: Wed Jul 14, 2004 8:09 am
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?

Posted: Wed Jul 14, 2004 8:19 am
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?

Posted: Wed Jul 14, 2004 10:06 am
by feyd
redmonkey wrote:something which (if a remember correctly) is not available in MySQL
IIRC, MySQL 5.0 has sub-selects.. :)

Posted: Wed Jul 14, 2004 10:13 am
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:

Posted: Wed Jul 14, 2004 10:29 am
by feyd
all to true.. :( :D

Posted: Thu Jul 15, 2004 3:40 pm
by liljester
mysql 4 supports Insert ... Select Statements

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