champ null dans une requête update

[hammag]

bonjour,

je cherche à updater une table (tab1) en prenant des valeurs dans la table tab2.

Code :

1
2
3
4
5
6
7
8
9
 
UPDATE tab1 A 
  SET (A.COUNTRY, A.ADDRESS1) = ( 
 SELECT COUNTRY 
 , ADDRESS1 
   FROM tab2 B 
 WHERE AND B.ORDER_NO =A.ORDER_NO
   AND B.SRC='xx' 
   )

le problème c'est quand l'un des champs (ou tout les deux) COUNTRY et ADDRESS1 sont null dans la table tab2 je ne peux pas updater la table 1 (champs non nullable dans tab1).

Code :

1
2
 
01407. 00000 -  "cannot update (%s) to NULL"

comment modifier cette requête pour enpêcher l'update en cas de champs nulls.

merci d'avance

[laurentschneider]

en ajoutant une clause where

Code :

1
2
3
4
5
6
7
8
UPDATE tab1 A 
  SET (A.COUNTRY, A.ADDRESS1) = ( 
 SELECT COUNTRY  , ADDRESS1    FROM tab2 B  WHERE B.ORDER_NO=A.ORDER_NO  AND B.SRC='xx') 
WHERE EXISTS (
 SELECT *  FROM tab2 C
 WHERE C.ORDER_NO =A.ORDER_NO  AND C.SRC='xx' 
  AND C.COUNTRY IS NOT NULL  AND C.ADDRESS1 IS NOT NULL 
   )

[hammag]

malheureusement j'ai toujours le même problème.

[laurentschneider]

donne nous un test-case, chez moi ça marche...

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE tab1(
   order_no   NUMBER PRIMARY KEY,
   country    VARCHAR2 (2) NOT NULL,
   address1    VARCHAR2 (20) NOT NULL
);
INSERT INTO tab1  VALUES (1, 'CH', 'rue de la Blonde');
INSERT INTO tab1 VALUES (2, 'CM', 'rue de la Joie');
CREATE TABLE tab2(
   order_no   NUMBER PRIMARY KEY,
   country    VARCHAR2 (2),
   address1    VARCHAR2 (20),
   src        VARCHAR2 (2)
);
INSERT INTO tab2  VALUES (1, 'CH', 'rue de la Brunette', 'xx');
INSERT INTO tab2 VALUES (2, 'CM', NULL, 'xx');

Code :

1
2
3
4
5
6
7
8
9
SQL> UPDATE tab1 A
  2     SET (A.COUNTRY, A.ADDRESS1) =
  3            (SELECT COUNTRY, ADDRESS1
  4               FROM tab2 B
  5              WHERE B.ORDER_NO = A.ORDER_NO AND B.SRC = 'xx');
   SET (A.COUNTRY, A.ADDRESS1) =
                   *
ERROR at line 2:
ORA-01407: cannot UPDATE ("SCOTT"."TAB1"."ADDRESS1") TO NULL

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL> UPDATE tab1 A
  2     SET (A.COUNTRY, A.ADDRESS1) =
  3            (SELECT COUNTRY, ADDRESS1
  4               FROM tab2 B
  5              WHERE B.ORDER_NO = A.ORDER_NO AND B.SRC = 'xx')
  6   WHERE EXISTS
  7            (SELECT *
  8               FROM tab2 C
  9              WHERE     C.ORDER_NO = A.ORDER_NO
 10                    AND C.SRC = 'xx'
 11                    AND C.COUNTRY IS NOT NULL
 12                    AND C.ADDRESS1 IS NOT NULL);
 
1 row updated.

[Waldar]

Un peu plus court à partir de la 10g :

Code :

1
2
3
4
5
6
7
8
9
MERGE INTO tab1 A 
USING tab2 B
   ON (B.ORDER_NO = A.ORDER_NO
  AND  B.SRC      = 'xx'
  AND  B.COUNTRY  IS NOT NULL
  AND  B.ADDRESS1 IS NOT NULL)
 WHEN MATCHED THEN UPDATE
  SET A.COUNTRY  = B.COUNTRY
    , A.ADDRESS1 = B.ADDRESS1;