Move Data In a Database Table

[millsy007]

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?

[Old Pedant]

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?

[millsy007]

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

[Old Pedant]

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:

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

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

[guelphdad]

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!