Inserting into same table

[jude99]

How can I insert data into the same table, please, as in duplicate 1d = '2'?

 

[jude99]

To explain perhaps a little better about repeating the row in the same table ... id '4' has information I want to duplicate (so I can make alterations to the orginal).

If I try ...

UPDATE `table1` set id '4' = id '5';

it doesn't work. How should it be coded, please?

 

[r937]

INSERT
  INTO table1
     ( id
     , somecol )
SELECT 5
     , somecol
  FROM table1
 WHERE id = 4

r937.com | rudy.ca

 

[jude99]

Thanks very much, Rudy. I adapted your code because I wanted to duplicate ALL of the contents of that particular field. I made it
------

INSERT
INTO table1

SELECT *
FROM table1
WHERE id = 4 ;
-------

and it worked. Does that look the right way to code it when it is all of a field, please?

 

[r937]

no, that's not right

you seem to be missing a primary key

r937.com | rudy.ca

 

[jude99]

Sorry, r937, then I am a bit more lost than I thought. The primary key in the table is the id field. Sorry, but I don't know how I would use it for this query. Could you show me the way, please?

 

[r937]

if id is the primary key field, then this --

INSERT
  INTO table1
SELECT *
  FROM table1
 WHERE id = 4

cannot possibly work


since SELECT * includes every column, it includes the id column

you would therefore be attempting to add another row with id=4, and since id is the primary key, that would produce an error

go back to my first post in this thread to see how to add another row, using 5 as the new id, by pulling everything from the row where id=4 except the id


r937.com | rudy.ca

 

[jude99]

Sorry to belabour this. But it is important because I have long wanted to be able to do it, and often need to do it.

INSERT
INTO table1
( id
, name1,name2,name3, another4 )
SELECT 5
, name1,name2,name3, another4
FROM table1
WHERE id = 4

Do you mean like that, please? Listing each column?

I suspect that the id column in the table from this morning isn't the primary key and that must be why it worked perfectly for me. I've just tried to auto increment the id row and there's an error message, so I can't have made it the primary key after the last time the id row was deleted.

Hope you don't mind helping with this. It is appreciated.

 

[r937]

you've got it -- the new id value is 5, and you have to list all the other columns



r937.com | rudy.ca

 

[jude99]

Thanks very much, r937. Very grateful.