*
The moose likes JDBC and the fly likes Any one have idea regarding this query?? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Any one have idea regarding this query??" Watch "Any one have idea regarding this query??" New topic
Author

Any one have idea regarding this query??

Ahamed Sharif
Ranch Hand

Joined: Aug 24, 2003
Posts: 47
Hi,

I am trying to insert a value from table1 to table2.
table1:
name address city
Ahamed XYZ Germany
table2:

name address city
Rose XXYYZZ ?
Which SQL statement i have to use for inserting table1 value 'Germany'
into table2 column city ?? looking for solution...
I am using MSAccess 2000 and jdk1.4. Help would be appriciated.
Thanks,
Ahamed.
Amit Agrawal
Ranch Hand

Joined: Aug 23, 2001
Posts: 282
so you want to insert City into table 2 irrespective of whether name or address matches in both tables or not?
Is there just one record in table1 ? if there are more than one record in table one, which city would you like to peak ?
Ahamed Sharif
Ranch Hand

Joined: Aug 24, 2003
Posts: 47
Hi,
In table2 city is already there, i want to insert the value of city from
table1 that is Germany.
The records of table2 Name and address is different but the city is same
from table 1 and table 2.

There are more records in table1 and table2.

I tried this query like
String query = " insert into UserInfo (CITY) in 'C:\\User.mdb' select
CITY from RateInfo";
stmt.executeUpdate(query);
Its inserting the City value of the RateInfo table to the UserInfo table.
But the problem is its inserting the next row.So the first row the value
is showing null.Could you solve this problem.
Table2 is showing like when run that program:
Name Address city
XXXX YYYYYY
HHHHH UUUUU
Germany
Germany

Thanks,
Ahamed.
Timothy Marks
Greenhorn

Joined: Sep 08, 2003
Posts: 4
Do you mean
sql = "Update table2 set city = '" + cityvariable + "' where name = '" +
namevariable + "';";
stmt.executeUpdate(sql);
?
Just need to read city name into a variable and make sure that the name in namevariable is unique, but the sql should work.
Ahamed Sharif
Ranch Hand

Joined: Aug 24, 2003
Posts: 47
Hi,
I think this query is not fit to my problem.
Just I have to insert a colum values of table1 in table2. table1 and
table2 has many records.Like
Table1:
Name Address City
XXX YYYY ZZZZ
FFF HHH JJJJ

Table2:
Name Address City
UUU DDDD ???
KKK AAAA ???

Firstly in table2 there is no city value, so i need the query to insert
the value of city from table1. Finally the table2 look like the following.
Table2:
Name Address City
UUU DDDD ZZZZ
KKK AAAA JJJJ
So, how can I insert the value of city like ZZZZ in table2 from a first
row onwards.In table2 the name and address is different than table1.
Thanks,
Ahamed


Originally posted by Timothy Marks:
Do you mean
sql = "Update table2 set city = '" + cityvariable + "' where name = '" +
namevariable + "';";
stmt.executeUpdate(sql);
?
Just need to read city name into a variable and make sure that the name in namevariable is unique, but the sql should work.
Timothy Marks
Greenhorn

Joined: Sep 08, 2003
Posts: 4
When you execute the sql statement
"insert into UserInfo (CITY) in 'C:\\User.mdb' select CITY from RateInfo"
you are creating a new row in your database with the column CITY filled out, but the name and address will get the values of null.
If you were to use
"update UserInfo set CITY = (select CITY from RateInfo) where Name = '" +
theNameInTheRowThatNeedsToBeUpdated + "';"
you would change the value in the CITY column of UserInfo to the first result returned by the select statement.
From your description, this looks like what you want to do. Have I understood your problem correctly?
Ahamed Sharif
Ranch Hand

Joined: Aug 24, 2003
Posts: 47
Hi,
If I used firt query like
"insert into UserInfo (CITY) in 'C:\\User.mdb' select CITY from RateInfo"
its inserting the city value but remailning null, is it correct.
If I used second query like
"update UserInfo set CITY = (select CITY from RateInfo) where Name = '" +
theNameInTheRowThatNeedsToBeUpdated + "';"
its showing the runtime error like
SQLException: [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot find the input table or query 'UserInfo'. Make sure it exists and that its name is spelled correctly.
If I used both queries like insert and update then the table2 is showing
like that.
Name Address City
ZZZZ
AAAA
UUU DDDD
EEE QQQQ
I am not getting the correct way, please watch my source code.
public static void main(String args[]){
// String url = "jdbc dbc:user"; // Second DSN name for UserPer.mdb
String url1 = "jdbc dbc:rate"; //First DSN name for RatePer.mdb
Connection con;
Statement stmt;

try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch (java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
// con = DriverManager.getConnection(url);
con = DriverManager.getConnection(url1);
stmt = con.createStatement();
//String query = " insert into UserInfo(City) in 'C:\\UserPer.mdb'
//select City from RateInfo ";
String query1 = " update UserInfo set City = (select City from
RateInfo) where Name='"+Ahammad+"';";
// stmt.executeUpdate(query);
stmt.executeUpdate(query1);
}
stmt.close();
con.close();
}
catch (SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}

}

help would be apprciated,
Thanks,
Ahamed.


Originally posted by Timothy Marks:
When you execute the sql statement
"insert into UserInfo (CITY) in 'C:\\User.mdb' select CITY from RateInfo"
you are creating a new row in your database with the column CITY filled out, but the name and address will get the values of null.
If you were to use
"update UserInfo set CITY = (select CITY from RateInfo) where Name = '" +
theNameInTheRowThatNeedsToBeUpdated + "';"
you would change the value in the CITY column of UserInfo to the first result returned by the select statement.
From your description, this looks like what you want to do. Have I understood your problem correctly?
Ahamed Sharif
Ranch Hand

Joined: Aug 24, 2003
Posts: 47
Hi Timothy Marks ,

waiting for ur response and please solve my problem and see another
new forum i posted.
Thanks,
Ahammad.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Any one have idea regarding this query??