Copying records from same table

[baffled_in_UK]

Hi,

I want to copy records into a table from values already in there with a value change ($new_value). My code looks like this :

mysql_query($sql = "INSERT INTO table (field1,field2,field2) SELECT '$new_value', field2, field3  FROM table WHERE key = "\$key_value\" ");

This doesn't write any records, I'm assuming I have a small (or major) syntax problem but cannot find it. I've searched the forums and looked at mysql.com but no luck.

Any help is appreciated.

Thanks

[bastien]

Add error handling into the mix to see exactly what is wrong

mysql_query($sql = "INSERT INTO table (field1,field2,field2) SELECT '$new_value', field2, field3 FROM table WHERE key = "$key_value" ") or die ("Can't query because ".mysql_error());

[mourisj]

Is $new_value set as you expect it?

echo() your query before executing it, this might show up some errors!

$sql = "INSERT INTO table (field1,field2,field2) SELECT '$new_value', field2, field3 FROM table WHERE key = "$key_value" ");
echo $sql;
$result = mysql_query($sql);

BTW: aren't the double-quotes around $key_value your problem??

JJ Mouris

[baffled_in_UK]

Thanks for taking the time to answer.

No errors thrown out.

The double quotes is a quirk of the forum, the actual code I keyed was "\$key_value\"

To get around this problem I have temporarily created a copy of the table with the records in I need... this works. Does this mean you can't copy from the same table you are inserting into ?

[mourisj]

I tested this situation on my system:

Create table t1 (
f1 int,
f2 varchar(10),
f3 varchar(10));

Filled this table with a couple of data.

Then executed the following query (on the command line, not in some PHP script, which adds the risq of other errors):

insert into t1 (f1, f2, f3) select '10', f3, f2 from t1;

Result:

Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from t1;
+------+----------+----------+
| f1 | f2 | f3 |
+------+----------+----------+
| 1 | valeur 1 | VALEUR 1 |
| 2 | valeur 2 | VALEUR 2 |
| 3 | valeur 3 | VALEUR 3 |
| 4 | valeur 4 | VALEUR 4 |
| 10 | VALEUR 1 | valeur 1 |
| 10 | VALEUR 2 | valeur 2 |
| 10 | VALEUR 3 | valeur 3 |
| 10 | VALEUR 4 | valeur 4 |
+------+----------+----------+
8 rows in set (0.00 sec)

This was done in mysql version 4.0.18 (Linux)

My reference book - which covers "only" version 3.22 - (MySQL&msql from O'Reilly&Associates) stated "... you cannot insert into the table you are selecting from".

So, this is definitely version-dependent!

JJ Mouris

[baffled_in_UK]

I'm on v4.0.12 so it should work. I'll take a closer look tomorrow. Thanks for all your help.