FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


SQL UPDATING TABLE





vickriz
Hi there i'd like to for some help to update my table something line this:
++++++++++++++++++++++++++++++++++++++++++++++++
OLD.NAME OLD.SIZE NEW.NAME NEW.SIZE
SILK SCREEN 2" STATPACKS SILK SCREEN STATPACKS 2"
SILK SCREEN 4" STATPACKS SILK SCREEN STATPACKS 4"
SILK SCREEN 5.5" STATPACKS SILK SCREEN STATPACKS 5.5"
SNAP RING 14mm SNAP RING 14mm
SNAP RING 2.5mm SNAP RING 2.5mm
SNAP RING 3.5mm SNAP RING 3.5mm
++++++++++++++++++++++++++++++++++++++++++++++++

I have thousands of records like this and kinda hard to edit them one-by-one. Can anyone give me SQL script to update this records?
Your help will appreciated very much.

PS: Im using MS ACCESS
hexkid
I have no idea if this works in MS ACCESS; if it accepts standard SQL, it works.

I would solve that problem by changing each line to
Code:
update my_table set name='NEW.NAME', size='NEW.SIZE' where name='OLD.NAME' and size='OLD.SIZE';
and then use the new format to update the database. To convert the file, I'd probably use only my editor or, if it proved too difficult to identify the columns, I'd write a PHP script to convert it.
The PHP script (meant to be run on the command-line) would be something like
Code:
#!/usr/bin/php
<?php
function convert($line) {
  ### old.name old.size new.name new.size
  ### update table set name='new.name', size='new.size' where name='old.name' and size='old.size';
  if (preg_match('/^(.*)\s(\d\S*)\s(.*)\s(\d\S*)$/', $line, $matches)) {
    $old_name = addslashes($matches[1]);
    $old_size = addslashes($matches[2]);
    $new_name = addslashes($matches[3]);
    $new_size = addslashes($matches[4]);
    echo "update table set name='$new_name', size='$new_size' where name='$old_name' and size='$old_size';\n
  }
}

while ($line = trim(fgets(STDIN))) {
  echo convert($line);
}
?>
vickriz
Thanks for your help hexkid. But your suggestion was very complicated for me.

All I want to do is to trim the field Name and then update the size to which the string that has been trimmed from the Name.

Im looking for variant of string function to clean and update my table.

Please help..
kv
As far as I understand vickriz doesn't have fields

OLD.NAME OLD.SIZE NEW.NAME NEW.SIZE

in the table. He is just giving an example. He wants to update the table with the new values as he has mentioned in the example.

The quickest way to do this is to export the table into an excel sheet, "find/replace" strings suitably. Then remove the contents of existing table and import the excel sheet with new values back.

Make sure you have a backup of the table, in case something goes wrong.
Related topics
Help: SQL error after uploading new files(thread updated)
MySQL Database
[sql] Get the structure of a table
MySQL problem (as usual)
could not connect to the database
Error trying to display SQL statement in html table
Error while creating the SQL table pls help
Could someone create a really simple login system???
Help needed with chained selects
Updating multiple MySQL rows at once
oracle sql Alter table from a triggered package procedure
Array Update SQL script not working
Problem with code that handles arrays and database updates
How to make this code simple?
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.