• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Winston Liek
Ranch Hand
Posts: 174
  • Mark post as helpful
  • send pies
  • 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

 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • 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: 174
  • Mark post as helpful
  • send pies
  • 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?

 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • 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: 174
  • Mark post as helpful
  • send pies
  • 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
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • 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: 174
  • Mark post as helpful
  • send pies
  • 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
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • 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: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • 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: 174
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
wow thanks for roel and dave for helping me.

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