• 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

Is this possible using SQL statements? Copying data from another table with same data?

 
Ranch Hand
Posts: 197
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm not sure if is this possible using a single SQL statement in JDBC. This is a sample:

Table 1:
ID NO | NAME | AGE
1 | MAI SAM | null
2 | GEORGE SMITH | null

Table 2:
ID NO | NAME | AGE
1 | MAI SAM | 27
30 | GEORGE SMITH | 30

What I want is to:
1. If primary exists in TABLE 2, if found, insert value of its age to TABLE 1 (From this sample, since ID = 1 exists in Table 2, copy age 27 to Table 1)
2. If not exists, check if exact name exists in TABLE 2. If found, insert its age to TABLE 1 (From this example, GEORGE SMITH is found in TABLE 2 so insert 30 to GEORGE SMITH of TABLE 1)

In this case, after I execute the query the resulting TABLE 1 would be:
Table 1:
ID NO | NAME | AGE
1 | MAI SAM | 27
2 | GEORGE SMITH | 30

Can someone experts in SQL help me please... TIA

 
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
And is it really required to have just 1 sql statement? Because with 2 sql statements it's fairly easy to get this done.

Which database are you using? Because the syntax differs quite a bit.
 
Winston Liek
Ranch Hand
Posts: 197
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for reply.

I am using Mysql. I'm wondering what's the efficient way to do this since the data in the table is more than a thousand
Do you have an idea how to do this if multiple sqls can be used?

 
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Winston Liek wrote:Thanks for reply.

I am using Mysql. I'm wondering what's the efficient way to do this since the data in the table is more than a thousand
Do you have an idea how to do this if multiple sqls can be used?



A thousand rows is nothing in a properly indexed table. In your case, indexes on ID (already there if ID is the Primary Key), and NAME.

As a WAG I would say the two SQL statements would be no slower than trying to shoehorn them into a single statement. In any case (and at least as important), they'd be a lot easier to read and understand.
 
Winston Liek
Ranch Hand
Posts: 197
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hi, can you please give me idea how can I do it? I really do not know how can I use only 2 sql.

What I'm thinking is to :
1. retrieve table 2 data
2. for each table 2 row, execute select statement to check if it exist in table1
3. execute update statement if a row is found

Ans also, I think my bottle neck would be on step 2 as I will be executing select statement each record.
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
UPDATE Table_A a
JOIN Table_B b ON a.ID = b.ID
SET a.whatever column = b.whatever_column
WHERE a.whatever column IS NULL;

Modify the relevant columns for each of the two statements.

The basic rule with JDBC is "If it can be done in the db, then do it in the db".
 
Winston Liek
Ranch Hand
Posts: 197
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In my example, is this correct:

SQL 1:
UPDATE Table1 tbl1
JOIN Table2 tbl2 ON tbl1.ID = tbl2.ID
SET tbl1 .age = tbl2.age
WHERE tbl1 .age is NULL


SQL2:
UPDATE Table1 tbl1
JOIN Table2 tbl2 ON tbl1.name= tbl2.name
SET tbl1.age = tbl2.age
WHERE tbl1.age is NULL

and also what does your quote mean?
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Winston Liek wrote:In my example, is this correct:

SQL 1:
UPDATE Table1 tbl1
JOIN Table2 tbl2 ON tbl1.ID = tbl2.ID
SET tbl1 .age = tbl2.age
WHERE tbl1 .age is NULL


SQL2:
UPDATE Table1 tbl1
JOIN Table2 tbl2 ON tbl1.name= tbl2.name
SET tbl1.age = tbl2.age
WHERE tbl1.age is NULL
[/code]


Those look like they'll do what you want, but test them out on a test db.

Winston Liek wrote:
and also what does your quote mean?



The database code is designed for this sort of thing, so getting stuff out of the db to then manipulate it in Java code (along with additional queries) before putting it back is almost invariably slower, and by a large margin.
Hence, do it in the database if you can.
A database is more than somewhere to store data. It is also a tool for manipulating that data, in large quantities.
 
Roel De Nijs
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Winston Liek wrote:I am using Mysql. I'm wondering what's the efficient way to do this since the data in the table is more than a thousand
Do you have an idea how to do this if multiple sqls can be used?


For you 1000 records seems a lot, for any database it's less than peanuts. Some of our tables have 300-400 thousands records and most queries finish in just milliseconds. So multiple sql queries can definitely be used.

Winston Liek wrote:In my example, is this correct:

SQL 1:
UPDATE Table1 tbl1
JOIN Table2 tbl2 ON tbl1.ID = tbl2.ID
SET tbl1 .age = tbl2.age
WHERE tbl1 .age is NULL


SQL2:
UPDATE Table1 tbl1
JOIN Table2 tbl2 ON tbl1.name= tbl2.name
SET tbl1.age = tbl2.age
WHERE tbl1.age is NULL


Those queries look good, but you'll only know if they work as expected if you run them to some test data.

One tip: the more you restrict your join-clause, the more performant your query will be. Although I doubt if you would notice the difference with just 1000 records (and maybe the query optimizer will do some optimizations itself).

SQL1:

In SQL2 you should add an extra condition (which verifies ids of table1 and table2 aren't equal):

Hope it helps!
Kind regards,
Roel
 
Winston Liek
Ranch Hand
Posts: 197
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
wow thanks for roel and dave for helping me.

@Roel
thanks, I will use your advice in join clauses
 
reply
    Bookmark Topic Watch Topic
  • New Topic