|
Posted by David Portas on 09/08/07 19:11
"Hamilton sucks" <caof@mcmaster.ca> wrote in message
news:1189273920.752746.25660@o80g2000hse.googlegroups.com...
>
> The reason for copying records is that I
> need to change them to new records, which are exactly the same as the
> old records except one column value.
Then they are not exactly the same. Insert the new rows together including
the NEW column value(s). That way you can add any relevent candidate key
constraints to your table - something that wouldn't be possible if the table
had to support transitional "copies" of the old data. Example:
INSERT INTO tbl (col1, col2, col3)
SELECT col1, @new_col2, col3
FROM tbl
WHERE ... ? ;
> Maybe I should consult about the design. The table location holds the
> location info about the local schools, each of them is linked to a
> test group and a set of students. If I want to add a new test group,
> which contains the same set of locations except that they are linked
> to the new group. Should I create a new table or add new records into
> tbl_location? The same problem holds for tbl_students as well since in
> the new group, all students' status must be reset.
>
This sounds very like a multi-valued or join-dependency situation. Are you
familiar with the Fourth and Fifth Normal Forms? If not then look up some
examples. You should satisfy yourself about the design based on your own
understanding of the business rules. It's notoriously difficult to give
detailed design advice in an online discussion. (Easy to spot potential
problems but hard to suggest the right solutions).
--
David Portas
[Back to original message]
|