Hi Guys

I have a MYSQL database on my Linux box. This database has one table which has following structure.

mysql> describe data;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| S.No. | int(5) | NO | PRI | NULL | auto_increment |
| ID | varchar(255) | YES | | NULL | |
| PASS | varchar(255) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Now in this table there are many rows which have password value equal to NULL. I have a file which is generated from a log file and this file has decoded password values in the following format.
i.e
emailid pass

e.g
talat@xyz.com talat
abc@xyz.com abc123

Can some one tell me how can i update my table via some script (or any other possible way) by first searching the the email id (from table) of password NULL in the file and then update its password column value.

Thanks in advance.


Regard
Talat

You could use statements something like:

UPDATE data SET PASS='talat' WHERE ID='talat@xyz.com' AND PASS IS NULL;

A simple way without having to write a script, is to copy your text file and paste into OpenOffice spreadsheet. Then you can swap the two columns so that pass is before the email. Next you can add a column before with "UPDATE data SET PASS='" as the text, add a column in the middle with "' WHERE ID='" and finally a column at the end with "' AND PASS IS NULL;"

Then if you copy/paste that into gedit, you'll have an SQL script you can run from mysql.

Not the most elegant way, but works if you aren't able to quickly whip up a script.

Dear MicahCarrick,

Thanks for the reply ,but the situation is like this. There are many unique email ids in the table some have their passwords and some ids passwords are NULL (unknown). From the file(separate file) which is extracted from log file contain many line (repetitive also) from them if any of the email id of our DB table occurs with its password them i have to update the table and update its password column. Following is the example of table and the file values.

Table

S.NO id pass
1 talat@xyz.com talat
2 abc@xyz.com abc123
3 shane@xyz.com NULL
4 alex@xyz.com alex
etc

File

talat@xyz.com talat
alex@xyz.com alex
talat@xyz.com talat
talat@xyz.com talat
shane@xyz.com shane123
etc

So far what i understand is this that i have to write a loop which pick the
first email id of password= Null ,then search that email id in the file if it exist in the file then pick the pass value and update the DB table pass column value and then move to next email id with pass=NULL .If that id is not found in the file then move to next email id.

But so far not been able to achieve it.

Regard
Talat

Did I understand you right? There is no diiference whether the password is NULL or not and db can be updated without considering it (that the password is NULL)?

Hi Alien_Hominid

NULL is the value of the pass column for those ids whose passwords are unknown to me. So your right and i only have to update those rows whose password value is = Null.

Regard
Talat

I would do it like this:

Import the file with all the e-mail password combinations into an additional table. (I hope that all equal e-mail addressed have the same passwords) Call this table email, use column names "mail" and "pass".

Then issue this SQL statement:

Note that data.pass = NULL or data.pass = 'NULL' does NOT work, you can't match the NULL value, you have to use the isnull() function.

Be sure to have a backup of your database, one error and you wipe out all the passwords you already have!

In the future, PLEASE use [code] tags around your table spec. You get them by pressing the sharp symbol (#) on top of this window. I was not able to read your table specification.

jlinkels

jlinkels sollution of course is more effective, but because there is no difference whether the password is already set or not (NULL), you can also do it without where condition:
This will work provided that each user in the log file has provided correct password.

Nice solution by jlinkels.

Gr8 idea guys Thanks

Regard
Talat