CODEWITHSUNDEEP
mysqlsql
Programmer
Programmer

Reputation: 1294

How to perform sql operation to insert data into a second table based on primary key matching to another table?

Sorry if the title is not clear. Basically I'm trying to achieve the following:

A very simple example. I have two tables with two columns each

Table 1:

Name: Fred, David, Alex, Jim, Mike

Chocolates: 1, 5, 3, 2, 4

Table 2:

Name: Fred, David, Alex, Jim, Mike

Lollipops:

Say I want to fill the Lollipops columns so that each person has ONE more lollipop than they have chocolates. i.e. Lollipops: 2, 6, 4, 3, 5. Where Name is a primary key in both tables. How to achieve this using sql query and java servlets please? Thanks

Upvotes: 0

Views: 38

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use this query:

update table2 t2
join table1 t1 on t2.Name = t1.Name
set t2.Lollipops = t1.Chocolates + 1

Demo here

Upvotes: 1

Related Questions