Update Replace query

[aeroswat]

UPDATE table SET field = replace(field, ';', ':') WHERE field LIKE '%;%';

 

Just curious if this would be the correct syntax to replace all my semi-colons in my 'field' column and also is there a way where I could replace ALL semi-colons in ALL columns with colons. With one query that is

[F1Fan]

I think that's correct, but that is completely dependent on your SQL type. You'd have better luck posting in the appropriate SQL board, not in the generic PHP board, especially since this is only a SQL question.

[aeroswat]

  Quote

I think that's correct, but that is completely dependent on your SQL type. You'd have better luck posting in the appropriate SQL board, not in the generic PHP board, especially since this is only a SQL question.

 

Thank you. Ya I thought about that also but I mean I know there is always a php way of accomplishing this and perhaps if somebody had recommended a way to do it in PHP that was faster I could just go ahead and throw up a quick page to run a loop that would take care of this.

[F1Fan]

Well, you could do it with PHP, but it would be significantly more complicated and slower. The reason for this is that you would have to extract the database data first, then run the update query. Plus, you would have to run as many update queries as there were rows. For instance, if you had 1,000 rows, you would have 1,001 queries; one select query to get the data and 1,000 update queries to update the rows. With your original solution, just one query is required, regardless of how many rows there are.

[Maq]

This should definitely be done in SQL.  I'm moving to the MySQL section unless you specify otherwise.

[aeroswat]

  Quote

Well, you could do it with PHP, but it would be significantly more complicated and slower. The reason for this is that you would have to extract the database data first, then run the update query. Plus, you would have to run as many update queries as there were rows. For instance, if you had 1,000 rows, you would have 1,001 queries; one select query to get the data and 1,000 update queries to update the rows. With your original solution, just one query is required, regardless of how many rows there are.

 

well i mean i figure i could just run a query that pulls the field names if this is possible? And i could just have a loop that runs one for each field name.

[Maq]

  Quote

  Quote

Well, you could do it with PHP, but it would be significantly more complicated and slower. The reason for this is that you would have to extract the database data first, then run the update query. Plus, you would have to run as many update queries as there were rows. For instance, if you had 1,000 rows, you would have 1,001 queries; one select query to get the data and 1,000 update queries to update the rows. With your original solution, just one query is required, regardless of how many rows there are.

 

well i mean i figure i could just run a query that pulls the field names if this is possible? And i could just have a loop that runs one for each field name.

 

Like F1Fan said, you would need a query to grab all of the rows then queries for as many rows you have to update them all.  Very inefficient.  Have you tried your original query?

[F1Fan]

Yes, that's basically what I said. Run a select to get the data, then loop through all the results and do an update for each row accordingly. But I don't suggest you do this. It will take significantly longer than just doing it in SQL. Why would you want to use PHP rather than SQL?

[aeroswat]

  Quote

Yes, that's basically what I said. Run a select to get the data, then loop through all the results and do an update for each row accordingly. But I don't suggest you do this. It will take significantly longer than just doing it in SQL. Why would you want to use PHP rather than SQL?

 

No no i mean run a for loop on the column names then for each do this query

 

UPDATE table SET field = replace(field, ';', ':') WHERE field LIKE '%;%';

 

so if i had 20 columns thats just 20 queries

[F1Fan]

Oh, you want to to that for every column? Then, yes, I suppose that would work. There may still be a way to do that in SQL.

[aeroswat]

  Quote

Oh, you want to to that for every column? Then, yes, I suppose that would work. There may still be a way to do that in SQL.

 

Hoping someone might be able to enlighten me on that SQL way

[fenway]

No without stored procedures there isn't.