• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Any one have idea regarding this query??

 
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 282
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 47
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 47
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 47
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 47
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Timothy Marks ,

waiting for ur response and please solve my problem and see another
new forum i posted.
Thanks,
Ahammad.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic