How to UPDATE field.

[siabanie]

Hi all,

 

I would like to ask if anyone could help me about updating table.

 

I have a table name: Property. And have 5 fields: ID, Branch, Cost, Type, Add.

 

In the Branch field I have 5 different values: A, B, C, D, E

 

Now I like to move ALL the data that I have in A  to D and delete the A.

 

I do not want to lost all the data I have in A nor in D - I just like to move A to D.

 

Is mySql query here correct?

 

mysql query

UPDATE table SET branch='D' WHERE branch='A';

 

Can anyone please assist me?

Thanks in advance..!

[Muddy_Funster]

You meen you havn't even tried it?

change table to property and it looks like it might.

[siabanie]

I have tried them but I got an error

 

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table SET branch='ゴルダーズグリーン' WHERE branch='フィンム

 

the "フィンãƒ" bit cannot be read as it's in different character (Japanese rather, the A, B, C, D, E are just an example)

 

Any idea what mistake I did here?

[Muddy_Funster]

What character encoding have you set in your database? It will need to be something that accepts multi lingual characters as input

[siabanie]

What character encoding have you set in your database? It will need to be something that accepts multi lingual characters as input

 

I have it set on utf8_general_ci - I think that is the mutlilingual char already?

[Muddy_Funster]

wait a minute - are you actualy trying to update a table called "table"?!? because that is a reserved word, you should call it something else.

[siabanie]

wait a minute - are you actualy trying to update a table called "table"?!? because that is a reserved word, you should call it something else.

 

Oh yes silly me!! you were right Muddy_Funster. I just run and it seems work now thanks.

 

Is that mean that all the data in A now been has been placed in D and rename it from A to D as well?

 

Thanks!

[Muddy_Funster]

Not exactly like that, no.  All that happend was you told the database that "FOR every record in 'table' that has an 'A' in the branch Field change this 'A' to be a 'D'"

So it hasn't moved or copied any records, only changed the information that that was stored in the branch column for every one that had an 'A' to begin with.

 

If you now run

 SELECT COUNT(*) AS branchCount FROM table WHERE branch = 'A'

You should get 0 back as the result.

[siabanie]

Not exactly like that, no.  All that happend was you told the database that "FOR every record in 'table' that has an 'A' in the branch Field change this 'A' to be a 'D'"

So it hasn't moved or copied any records, only changed the information that that was stored in the branch column for every one that had an 'A' to begin with.

 

If you now run

 SELECT COUNT(*) AS branchCount FROM table WHERE branch = 'A'

You should get 0 back as the result.

 

I see yes I got no record when I run that query - thanks.

 

So that means every time there is a new record in A I will have to run this UPDATE query then? - Can I just move all the records in A into D, isn't much better instead of having to update it every time there is a new record found in A?

 

What you think?

[Muddy_Funster]

Best approach would be to alter the insert statement if you can, so that no records will be added to 'A'.  Other than that, there is no other way that I know of than to run the update periodicaly.

 

You don't "move" records, you only change the data that the records hold.

[siabanie]

Best approach would be to alter the insert statement if you can, so that no records will be added to 'A'. 

 

Ok thanks!... What do you mean by that? Can you show me an example please.

 

Thanks

[Muddy_Funster]

I don't know how you are inserting your records, without knowing that I can't realy help much.

[siabanie]

I don't know how you are inserting your records, without knowing that I can't realy help much.

 

Hmmm I insert the records using PHP script something like this e.g:

 

..

$sql_query = "INSERT INTO

                ".TABLE_PRO."

                (`branch` ,`address``cost` ,`type`) VALUES (

                '$branch','$address','$cost','$type','')";

..

 

[Muddy_Funster]

then you could simply catch it in the PHP before it gets to the insert SQL:

 if($branch == 'A') {$branch = 'D';}

[fenway]

Or with a CASE statement.

[siabanie]

Great ideas guys, thanks a lot!

 

Will try and working on it!

Thanks!

[Muddy_Funster]

Or with a CASE statement.

 

Yip, it's a little more complicated though:

INSERT INTO table (
  `branch` ,`address``cost` ,`type`
)
VALUES (
CASE WHEN '$branch' = 'A' THEN SELECT 'D' ELSE SELECT '$branch' END,'$address','$cost','$type',''
)

Is how I think it would look, but I'm not sure about the use of SELECT...

[siabanie]

Or with a CASE statement.

 

Yip, it's a little more complicated though:

INSERT INTO table (
  `branch` ,`address``cost` ,`type`
)
VALUES (
CASE WHEN '$branch' = 'A' THEN SELECT 'D' ELSE SELECT '$branch' END,'$address','$cost','$type',''
)

Is how I think it would look, but I'm not sure about the use of SELECT...

 

Thanks...but yes quite tricky - the thing is I use array for my branch e;g

 

branch[]

 

So perhaps I can avoid these?

 

[fenway]

You'd have to case each field separately.

[Muddy_Funster]

So what are your actual variables? are they single value ($branch), single array per SQL variable ($branch[]) or all stored in a single array? ($insert[branch, address, cost, type])?