Hi everyone,
I try to write back quantity of data in MYSql, i need to pick out wrong data by checking if the input row has complied PK.
The way i am doing is using For loop and "select" statement and "result set" to process one inputed row at a time to see if PK not existing.
If there are easy way and fast way to process it and do exception control?
Thanks
The PK should be set automatically if it has the attribute AUTO_INCREMENT in an SQL database.
If the PK does not AUTO_INCREMENT you can use the statement
ALTER TABLE and set the PK field as e.g. rowID INT PRIMARY KEY NOT NULL AUTO_INCREMENT (the not null on a pk should be implicit anyways but it can't help to include it).
Then you can use the statement
UPDATE tablename SET rowID=NULL WHERE rowID=NULL
This will update the PK where there is currently a NULL PK value, however, in this instance is updated as an auto-incremented INT and not NULL ! (similar to the way TIMESTAMP works by saying timstampfield=NULL results in the time/date
If you want to set the PK field with a variable value, it would be more efficient to SELECT all the values where the PK is NULL, iterate through them but use a separate (prepared) statement to UPDATE each one. The prepared statement will be faster and you will know that every row in the resultset needs to be updated as you have only SELECT'd those with a 'wrong' PK
A word of warning: if teh AUTO_INCREMENT attribute is set, many DBMSs will not let you update the PK. 2nd, an error will be raised if you set the PK identical to another. 3rd, it is rare that you will need to explicitly manipulate PKs if the database is well-designed (an exception is where you are using a 'username CHAR' field and capability is needed for changing one's username).
Hope this helps,
Bamkin
Thanks your response, also sorry for multi-posting since i don't know where is good place to park my question.
But I don't use auto increment in this table.
update table1, table2 set table1.col2 ='boy', table2.col3 = '2001-05-02'
where table1.sid ='199' and table2.sid ='199';
I only want to update raw data if sid, PK key, exists on both tables. I wonder if possible to get wrong sid from DB while updating...may be through exception control
I don't know if there are efficiency way here.
The SQL command that you provided
update table1, table2 set table1.col2 ='boy', table2.col3 = '2001-05-02'
where table1.sid ='199' and table2.sid ='199';
will not update anything if sid does not exist on either or both sides.
However most databases including MySQL will not allow for a primary key to be non-existant (unless you did not declare sid as a primary key).
You can tell if the primary key oes not exist on one or both sides by using the integer return of JDBC...
int effectedHowManyRows;
effectedHowManyRows=statement.executeUpdate(update table1, table2 set
table1.col2 ='boy', table2.col3 = '2001-05-02'
where table1.sid ='199' and table2.sid ='199');
if (effectedHowManyRows<1) //no rows effected, sid does not exist on one or both sides;
{
//deal with the fact that both tables do not have a record '199';
}
if (effectedHowManyRows==1) //if sid is a primary key on one (or both) tables, no more than one record will ever be effected;
{
//col2 and col3 have been set for record '199';
}
I don't quite understand what you mean by "get(***) wrong sid from database while updating"...
If you could elaborate, I would be happy to help to the best of my ability.
Bamkin
> If you could elaborate, I would be happy to help to
> the best of my ability.
> Bamkin
Thanks your response. Sorry for my wording.
I generate one html page to ask operator to type in sid, but some times they have typo or duplicated sid. I have used RegEx to filter out sid has wrong format, i.e. 2 letters + 3 digit numbers . Next step before writing into DB, I need check out if sid exists in DB, if not I tread them as illeagal. Screen out all illeagal sid and back to html page, highlight those illeagal sid then ask operator retype again.
MySql will update data with leagal sid, I wonder if any easy way to screen out all illeagal and duplicat sids and I won't write to DB till all sids are leagal.
I hope i explain my question clearly.
Thanks
>> int effectedHowManyRows;
> effectedHowManyRows=statement.executeUpdate(update
> table1, table2 set
>table1.col2 ='boy', table2.col3 = '2001-05-02'
> where table1.sid ='199' and table2.sid ='199');
> if (effectedHowManyRows<1) //no rows effected, sid
> does not exist on one or both sides;
> {
> //deal with the fact that both tables do not have a
> record '199';
> }
> if (effectedHowManyRows==1) //if sid is a primary key
> on one (or both) tables, no more than one record will
> ever be effected;
>
>
If I update quantity of data, any alternative way to save DB resource?
I think that I will not regret saying that any database in which the user defines their own primary key is poorly designed.
If the user giving additional information (saved in a record in a new table) already has a record in another table, the DBMS should define it's own primary key then link to the existant table using a forign key (a field referencing the primary key of the corresponding record in the original table).
Why does the user have to define their own primary key ?
Bamkin
