Best Way To Update Intersection Tables

[tbemcf14]

Hi,

I'm surprised I haven't been able to find anything on this, but I was wondering if I could get some advice as to the most efficient way to update an intersection table, i.e. a M:N resolution table.

My example...

Say I have a M:N relationship between tables Cars and Colors. To resolve, I form table Cars_Colors with Car_ID and Color_ID as PFK's from their respective table.

So on my form, say we're dealing with record "Ford Ranger," I'll have a bunch of checkboxes with colors, in which multiple colors can be selected or de-selected. How should I handle this update?

Currently, I just delete all the existing colors for the specific car, and then re-add the list of colors from the form... basically a delete then insert on the table... I don't use any update sql. But this is a nightmare when trying to keep audit history on data changes in the table.

Any advice? Thanks in advance,
Mark

[MikerRoo]

See the attached.

Also, I'm required by law to inform you that this sort of thing should be done in a stored procedure. ;-)

[tbemcf14]

MikerRoo, thank you for your response... sorry for the delayed acknowledgement.

I'm trying your code, and the delete works fine, but Oracle is barking on the insert statement that the From keyword is not found where expected. It looks like it's missing the from keyword in the SELECT #sDesiredID_SQL# AS iNewColorID sub-select statement. Any ideas?

Thanks,
Mark

[paross1]

What version of Oracle are you running? You can't use ANSI SQL 92 syntax (JOIN statements) prior to Oracle 9i.

Phil

[tbemcf14]

8.1.7.0 (8i)... I realized the join statements are different in more recent versions... and I changed the query to use the old (+) method and still got the error.

What to do now?

[MikerRoo]

Grrr. You might have mentioned that this was a way old Oracle DB.

I'm not an Oracle expert and only have version 10 to play with.

Anyway, maybe you can split the insert using a temp table and get it to work that way?

[tbemcf14]

Thanks everyone for your help. Your code samples gave me the start to to get this working by using CF code that produced the following query in Oracle 8i:

INSERT INTO Cars_Colors (Car_ID, Color_ID)
SELECT 2, AA.iNewColorID
FROM
(
SELECT 1 AS iNewColorID FROM dual UNION
SELECT 2 AS iNewColorID FROM dual UNION
SELECT 4 AS iNewColorID FROM dual
) AA

WHERE AA.iNewColorID not in (
select color_id from cars_colors where car_id =2
)