Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Feb 2009
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Move Data In a Database Table

    I want to do a query to move data within a table.

    I am passing in a variable that is the column that I want the data to go into.
    I also know the data that I need to move.

    So If there was a table called Film, with the following values:

    Actor, Director, Producer
    Tarantino

    and in my query I was passing in
    $role = director
    and
    $person = tarantino

    ie I wanted the data to move from the actor column to the director for this example

    How could I do this in sql/code?


  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Sorry...that doesn't make sense.

    You show *THREE* columns: Actor, Director, Producer

    You show a VALUE for only ONE column: Tarantine

    Which column is "Tarantino" in ???

    And do you want to move *ALL* records which match your given criteria or just one row?

  • #3
    New Coder
    Join Date
    Feb 2009
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry that wasnt very clear, for this example the data (tarantino) is in the Actor Column.

    However what i want is to say is wherever the value 'tarantino' is in another column it should now be in the $role column.

    So
    Actor, Director, Producer
    Tarantino

    Should Become
    Actor, Director, Producer
    Tarantino

    After the query has run
    And yes this would be for all records

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Apparently you are typing in spaces, thinking that will show us what field a value is in. But spaces are IGNORED in HTML, so we just see what you would see if you looked at your own post.

    To preserve spaces, wrap those parts of your post in [ code ] .... [ /code ] tags.

    I am *GUESSING* that you mean:
    Code:
    EXISTING RECORD:
    Actor      Director     Producer
    xx         xx           Tarantino
    
    Then *IF* the fields given are:
    $role = director
    and
    $person = tarantino
    
    THAT SHOULD BECOME:
    Actor    Director    Producer
    xx       Tarantino   Tarantino
    Yes??? You don't say what to do with the field that the name was moved *FROM*, so I'm just leaving it as is.

    So... let's think about it...
    Code:
    UPDATE table
    SET $role = '$person'
    WHERE $role != '$person'
    AND '$person' IN ( Actor, Director, Producer )
    I think that works. It first looks for all records where 'Tarantino' is *NOT* the Director and then for all records where 'Tarantino' *IS* either actor or director or producer. (Naturally, the fact that we check director in the second test is essentially ignored because of the first test.) When it finds such a record, it changes the Director to be, as you asked, 'Tarantino'.

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    actually you should have two columns, 1 column for the name and the other denoting the roll. if tarantino is a producer, director and actor he should have three rows in the table.

    normalize your data is always the best bet!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •