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.