how to copy data from primary table and its dependencies from one id to another and then how could I remove primary table with its dependencies

  • Hello,

    I have one table called A. A has dependencies on B. B has dependencies on c. C has dependencies on D.

    A has dependencies on B,C,D.

    How could I copy A,B,C,D's data from id 1 to id 2?

    After copying data from id 1 to 2 , I want to remove id 1.

    Is there any script to remove it?

    Thanks

  • Virtual Reality (8/16/2016)


    Hello,

    I have one table called A. A has dependencies on B. B has dependencies on c. C has dependencies on D.

    A has dependencies on B,C,D.

    How could I copy A,B,C,D's data from id 1 to id 2?

    After copying data from id 1 to 2 , I want to remove id 1.

    Is there any script to remove it?

    Thanks

    Give us some sample DDL and INSERT statements with some sample data and we'll help with the code. Regarding your question, the answer is 'yes'.

    There will be a longer answer, if you provide the requested information.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • You could. Or you could change the FK rules to "CASCADE" rather than "NO ACTION" -- at least temporarily -- then just try updating the id from 1 to 2.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • >> I have one table called A. A has dependencies on B. B has dependencies on c. C has dependencies on D. A has dependencies on B,C,D. <<

    Please read what you wrote. The phrase "has dependencies" is too vague to be part of RDBMS. Instead, we have "referenced" and "referencing" tables. What role does table alpha play? We do not know. Next we do not know what the keys are. Sorry, but in RDBMS. There is no such crap is a generalized generic magic "id"; it has to be the identifier of something in particular. This concept of "something in particular" is a result of the founding principle of all Western logic called the law of identity (usually expressed as "a is a" or in a little more detail to be is to be something in in particular (have an identity), and to be everything in general or nothing in particular is to be nothing at all. Connector freshman logic book and read the section on the Greeks.

    In SQL we have DRI actions. This would automatically make some changes for you, if you had actually followed form rules and posted DDL.

    >> How could I copy A,B,C,D's data from id 1 to id 2? <<

    I think you have missed a really important point about databases, not just RDBMS. The reason we created these in the 1970's was to remove redundancy and not to increase it. We had our data in multiple copies all over Hell and creation! It was not working; we became the guy with two or three different wristwatches was never quite sure what time it was because nothing was ever synched.

    A reference is not a copy. This is particularly hard for T-SQL programmers to handle because the old Sybase product did have duplicate values. Other products do not! For example, in Watcom SQL, the foreign key was actually implemented as a pointer back to the single occurrence of this key value in the physical database. When that key value changed, the pointers automatically referenced that new value. It also led to very different kinds of join algorithms, etc.

    >> After copying data from id 1 to 2 , I want to remove id 1.<<

    Gee, just like moving punchcards from one stack to another :w00t: you do not understand the concept of a data and a reference to that data, but you do understand punchcards, records in a file, and other physical models. RDBMS is not based on physical models. If you want an analogy, think about Roman numerals. They were used record a physical fact, so they were only integers (with a limited set of fractions). Negative numbers and zero have no physical existence! So we do not represent them.

    If you will post some skeleton DDL (as per form rules. Please), then will try and write you some actual code. Perhaps the hardest thing for a new SQL programmer to learn is that 85 to 95% of the real work in this language is done in the DDL, not the DML. When you have to do basic things like this in the DML, your designs probably screwed up really bad.

    Books in Celko Series for Morgan-Kaufmann Publishing
    Analytics and OLAP in SQL
    Data and Databases: Concepts in Practice
    Data, Measurements and Standards in SQL
    SQL for Smarties
    SQL Programming Style
    SQL Puzzles and Answers
    Thinking in Sets
    Trees and Hierarchies in SQL

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply