Results 1 to 3 of 3
  1. #1
    Join Date
    May 2008
    Posts
    2

    Unanswered: Oracle update query problem

    I'm at a loss been trying for hours to do this but Oracle won't let me do something like this :

    Code:
    UPDATE A 
    	SET a.INTERField = CASE WHEN (SELECT COUNT(*) FROM dbo.table1 wHERE ID = B.ID) > 1 THEN 1 ELSE 0 END,
    	A.ID2 = CASE WHEN A.ID2 = 'BLABLA' AND (SELECT COUNT(*) FROM DBO.table2 WHERE ID = B.ID) > 0 THEN
    	B.ID2 ELSE A.ID2 END 
    FROM 
    	dbo.table1 A 
    FULL JOIN 
    	DBO.table2 B 
    ON 
    	A.ID = B.ID 
    WHERE 
    a.INTERField = 1 AND (A.ID2 = 'BLABLA' OR B.ID2 = 'BLABLA')
    The problem is I need to update both columns at the same time. Doing this in 2 updates is not possible as it would influence the result. Also, Oracle temp table wouldn't be possible as it requires special rights. So if anyone could help me translate this from SQL Server to oracle I would appreciate this.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Oracle won't let me do something like this :
    We don't have your tables.
    We don't have your data.
    We don't have any error code.
    We don't know exactly what results you ARE getting.

    Why do you expect any real assistance?

    You're On Your Own (YOYO)!

    My car is won't go.
    Tell me how to make my car go.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2008
    Posts
    2
    ok you were lacking context in any case, what I wanted to do is now fixed. Here's the ORACLE code :

    Code:
    update Table1 a
    set ID2 = (select		
    	   CASE WHEN Aa.id2= 'BLABLA' AND (SELECT COUNT(*) FROM table2 WHERE id = aa.ID) > 0 THEN
    	   		(select id2 from table2 where id = aa.id and rownum = 1) 
    	   ELSE 
    	   		aa.id2
    	   END
    from
    (select 
    	   aaa.id2,
    	   aaa.id
    from
    	   	table1 aaa,
    		table2 bbb
    where
    		aaa.interfield  = 1
    and
    		(aaa.id2 = 'BLABLA' or bbb.id2 = 'BLABLA')
    and
    		aaa.id = bbb.id
    group by
    	  	aaa.id2,
    		aaa.id) aa
    where
    	   aa.id = a.id
    ),
    a.interfield = (select		
    	   CASE WHEN (SELECT COUNT(*) FROM table2 WHERE ID = aa.ID) > 0 THEN 
    	   		1 
    	   ELSE 
    	   		0
    	   END
    from
    (select 
    	   aaa.id2,
    	   aaa.id
    from
    	   	table1 aaa,
    		table2 bbb
    where
    		aaa.interfield  = 1
    and
    		(aaa.id2 = 'BLABLA' or bbb.id2 = 'BLABLA')
    and
    		aaa.id = bbb.id
    group by
    	  	aaa.id2,
    		aaa.id) aa
    where
    	   aa.id = a.id)
    I needed to update Both fields at the same time.

    I had TABLE 1 with ID,ID2,Interfield
    and table2 with ID,ID2

    If interfield is 1 then a line in table2 exists. That table 2 line has same id as table 1 id but not the same id2.

    The idea here was to delete all rows in table1 and table2 with the criteria ID2 = 'BLABLA', but at the same time conserve the pseudo relation (table1, table2).

    So I had to

    1 - Get all lines concerning ID2

    2 - Once I got all lines, Update ID2 in Table1 so I keep a relation for those lines that stays there. So I had to update the ID2 of table 1 with one of the ID2 of table 2

    3 - If there's only 1 line in table 2, then I have to set the INTERFIELD to 0.


    So as you can see I needed to do this all at the same time as doing this in step could have been prone to errors. I know my code isn't fool proof for the pseudo relationship in regards to id2, but I just need 1 of the ID2 and not all of em (hence the group by).
    Last edited by Boumxyz; 05-20-08 at 11:46.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •