1

I have table1,table2 => all fields are identical except that table2 two has an extra field which is a FK of table1

**table1** *ID*,content,status
**table2** *ID*,content,status,tid

so tid = table1 id.

I need to copy a row from table1 one to table2 so esentially the table2 would be a backup of table1. I can do it using mysql,then php, then mysql again I was wondering if you could have a simpler solution on mysql :)

hope its not too complicated

Val
  • 17,336
  • 23
  • 95
  • 144

2 Answers2

1

If you want to copy every row in table1 into table2, you could do:

INSERT INTO table2 (id, content, status, tid)
  SELECT id, content, status, id FROM b;

If table2 isn't empty, you could add an ON DUPLICATE KEY... clause to deal with clashes.

EDIT

If you just want to copy one row, you can add a WHERE clause:

INSERT INTO table2 (id, content, status, tid) 
  SELECT id, content, status, id FROM b WHERE id=123;
Michael Pilat
  • 6,480
  • 27
  • 30
  • no its a single entry/record I need to copy, therefore I can have multiple versions of that record and still keep table1 clean if that makes sense – Val Apr 05 '11 at 13:45
  • ok, check out my edit. Is `id` the same in both tables, or e.g. `auto_increment`? – Michael Pilat Apr 05 '11 at 13:47
  • this sounds abit more promissing :) the edit part :) I will give it a go and get back to u :) – Val Apr 05 '11 at 13:47
  • well both id on table1 and table2 are autoinc... but tid is the same as `WHERE id=123` id – Val Apr 05 '11 at 13:54
0
INSERT INTO table2 (content, status, tid) SELECT content, status, ID FROM table1
tamasgal
  • 24,826
  • 18
  • 96
  • 135