Please validate the update [message #417353] |
Fri, 07 August 2009 09:59  |
ora1980
Messages: 251 Registered: May 2008
|
Senior Member |
|
|
CREATE TABLE TMP(CODE VARCHAR2(5), ID VARCHAR2(5))
INSERT INTO TMP VALUES('0','1')
/
INSERT INTO TMP VALUES('000','0')
/
INSERT INTO TMP VALUES('006','0')
/
INSERT INTO TMP VALUES('006','1')
/
CREATE TABLE MAIN(CODE VARCHAR2(5), ID VARCHAR2(5), DC NUMBER)
INSERT INTO MAIN VALUES('0','1',0)
/
INSERT INTO MAIN VALUES('000','0',0)
/
INSERT INTO MAIN VALUES('006','0',0)
/
INSERT INTO MAIN VALUES('006','1',0)
/
INSERT INTO MAIN VALUES('006','3',0)
/
INSERT INTO MAIN VALUES('006','4',0)
/
INSERT INTO MAIN VALUES('0','4',0)
/
INSERT INTO MAIN VALUES('8','1',0)
/
I want the column DC to be null in main table for all those ids and codes in tmp table
I gave this:
update main AA set AA.DC = null
where AA.CODE IN (SELECT CODE FROM TMP )
AND AA.ID IN (SELECT ID FROM TMP )
it does give 4 rows updated, but this is just example, i am working on a much bigger table with thousands
of rows, i want the actual combination of id and code to be updated..
example, there may be thousands of rows with id as 1, and code not in 0 and 006, they should not be updated
eg: 8,1,0 row in main table should not be updated/.
will the above update do ?
|
|
|
|
|