Re: Insert unique values problem. you messed up your correlated sub-query
insert into commontable(ordnum)
select
order_number
from
table1 t1
where
not exists(
select
*
from
commontable c
where
c.ordnum = t1.order_number
) ;
This should give you want you want.
OP/ED piece (without knowing anything else about the system and its
free so you know what it is worth ;-) )
What you should do his merge all of the order tables together with some
sort of translation so that all order numbers are unique:
select
order_number
from table1
union
select
order_number + 1000000
from table2
union
select
order_number + 2000000
from table3
....
Where the offset increases with each table and makes sure that the
records don't overlap. Then make this new table your uber-order table.
its order number should be a serial. Then create 10 views (todays
secret word) to back fit the rest of the system. You will now create
unique order numbers for your 10 different tables(views). Or if you are
using a version that has sequences then use one sequence to generate
the keys for all ten tables and then your update is very simple.
select * from table1 where ordnum > (select max(order_number) from
commontable);
select * from table2 where ordnum > (select max(order_number) from
commontable);
etc, etc ... |