need to frame a query

Database
N/A

need to frame a query

Hello All,

I have a requirement with me.

I got two tables. Lets say A and B.

lets say table A has columns ---------name, location, number,  code, id. unique primary index for this is (number, code)

and table B has columns---------- id, name, code, location, code. unique primary index for this is (id).

I need to join these two tables (join A and B )based on A.name = B.name and A.location = B.location

for every matching rows i need to update A's Id ------------A.id = B.Id

Imp part: For every non matching row i need to first insert a new row into B first.

insert into B

B.id = max(B.id) + 1,

B.code = A.code

and default values to other coloumns

and then update the  non matching row of table A with this new id value

i.e. A.id = B.id

for all such non matching rows i need to do this imp part.

Could anyone help me out with this.

Thanks in advance.