View Full Version : Move Data In a Database Table

03-05-2009, 02:34 AM
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

and in my query I was passing in
$role = director
$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?


Old Pedant
03-05-2009, 03:14 AM
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?

03-05-2009, 12:09 PM
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.

Actor, Director, Producer

Should Become
Actor, Director, Producer

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

Old Pedant
03-06-2009, 12:07 AM
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:

Actor Director Producer
xx xx Tarantino

Then *IF* the fields given are:
$role = director
$person = tarantino

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...

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'.

03-06-2009, 05:27 PM
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!