-1

Oracle database

Hi guys, i have trouble with oracle syntax and inner join in update i tried something like this but it doesn't work

"missing SET keyword"

UPDATE table1 AS t1
INNER JOIN table2 AS t2 ON t1.id_description = t2.id_description  
SET field = '0.0.0.1.5.' 
WHERE t2.code='XXXX' AND t2.status IN ('VALUE1','VALUE2');

thx a lot

funtocompute
  • 3
  • 3

3 Answers3

0
UPDATE t1
SET t1.field = '0.0.0.1.5.'
From table1 AS t1
INNER JOIN table2 AS t2 ON t1.id_description = t2.id_description  
WHERE t2.code='XXXX' AND t2.status IN ('VALUE1','VALUE2');
Alex
  • 371
  • 1
  • 8
0

That works in SQL Server, In oracle there is another syntax

UPDATE table1 t1
  SET field = '0.0.0.1.5.'
WHERE EXISTS (SELECT 'X'
                FROM table2 t2 
               WHERE t1.id_description = t2.id_description 
                 AND t2.code='XXXX' 
                 AND t2.status IN ('VALUE1','VALUE2'));
San
  • 4,508
  • 1
  • 13
  • 19
  • what 'X' means here ? seems to be working can i write this ? UPDATE table1 t1 SET field = '0.0.0.1.5.' WHERE EXISTS (SELECT 1 FROM table2 t2 WHERE t1.id_description = t2.id_description AND t2.code='XXXX' AND t2.status IN ('VALUE1','VALUE2')); is any 'cleaner' or 'smaller' solution to do this ? – funtocompute Oct 10 '14 at 13:16
  • Yes you can write it. 'X' here can be replaced by any valid string or number. The sub query only returns a true condition when `t1.id_description = t2.id_description` and other conditions are fulfilled. – San Oct 10 '14 at 13:27
  • thank you for your explaination, i keep your solution using INNER JOIN ;) – funtocompute Oct 10 '14 at 13:46
0

Try this:

UPDATE table1 t1
   SET field = '0.0.0.1.5.' 
 WHERE t1.id_description in (
         select t2.id_description 
           from table2 t2
          where t2.code='XXXX' 
            AND t2.status IN ('VALUE1','VALUE2')
       )
neshkeev
  • 6,280
  • 3
  • 26
  • 47