copy row but change one value 1

[dkemas]

I need to copy a row in a table but have the id auto increment and also change one of the values in the new row

I am very close, my sql will copy the row and increment the id, but how do I change one of the fields. Here is what I have that will copy the row with id 11, insert a new row with the next available id

INSERT INTO mytable SELECT 0, `field1`, `field2`, `field3`, `field4`, `field5`
FROM mytable WHERE id = 11

Is it possible to modify this to insert the value 'newvalue' into field4 but still copy the rest of the values?

Thanks

 

[feherke]

Hi

I am confused. You mean like this ?

[b]INSERT[/b] [b]INTO[/b] mytable [b]SELECT[/b] [purple]0[/purple][teal],[/teal] [green][i]`field1`[/i][/green][teal],[/teal] [green][i]`field2`[/i][/green][teal],[/teal] [green][i]`field3`[/i][/green][teal],[/teal] [green][i]'newvalue'[/i][/green][teal],[/teal] [green][i]`field5`[/i][/green] [b]FROM[/b] mytable [b]WHERE[/b] id [teal]=[/teal] [purple]11[/purple]

Feherke.
[link feherke.github.com/]

http://feherke.github.com/

[/url]

 

[dkemas]

Exactly that yes, but I would get the error

#1054 - Unknown column 'newvalue' in 'field list'

if I did that

 

[feherke]

Hi

#1054 - Unknown column 'newvalue' in 'field list'

That would mean, you are enclosing newvalue in backtick ( ` ). You have to enclose it in single quotes ( ' ).

Feherke.
[link feherke.github.com/]

http://feherke.github.com/

[/url]

 

[dkemas]

Aha I see, so backtick will insert the value of the field, single quotes will insert my value.

I wondered why phpmyadmin was using backticks after I execute a query.

Thank you Feherke

 

[feherke]

Hi

Aha I see, so backtick will insert the value of the field, single quotes will insert my value.

Yes, you can say so. In other words
[ul]
[li]backticks are used for identifier quoting ( see Language Structure | Literal Values | String Literals )[/li]
[li]single quotes are used for string quoting ( see Language Structure | Literal Values | Schema Object Names )[/li]
[/ul]

[small][blue][tt][off-topic][/tt][/blue][/small]
Personally I never understood this habit, popular among MySQL users, of quoting all identifiers. Backticks are required if you use reserved word to name a table, field or other object. For example like in this aberrant example :

[b]create[/b] [b]table[/b] [green][i]`create table`[/i][/green] [teal]([/teal] [green][i]`insert`[/i][/green] [maroon]int[/maroon][teal],[/teal] [green][i]`into`[/i][/green] [maroon]int[/maroon] [teal]);[/teal]

[small][blue][tt][/off-topic][/tt][/blue][/small]

Feherke.
[link feherke.github.com/]

http://feherke.github.com/

[/url]