This content has been marked as final. Show 6 replies
See the attached.
Also, I'm required by law to inform you that this sort of thing should be done in a stored procedure. ;-)
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?
What version of Oracle are you running? You can't use ANSI SQL 92 syntax (JOIN statements) prior to Oracle 9i.
126.96.36.199 (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?
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?
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
SELECT 1 AS iNewColorID FROM dual UNION
SELECT 2 AS iNewColorID FROM dual UNION
SELECT 4 AS iNewColorID FROM dual
WHERE AA.iNewColorID not in (
select color_id from cars_colors where car_id =2