Home » SQL & PL/SQL » SQL & PL/SQL » Please validate the update
Please validate the update [message #417353] Fri, 07 August 2009 09:59 Go to next message
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 ?
Re: Please validate the update [message #417355 is a reply to message #417353] Fri, 07 August 2009 10:16 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
UPDATE   main aa
SET      aa.dc = NULL
WHERE    (aa.code, aa.id) IN (SELECT   code,
                                       id
                              FROM     tmp)



how about this
Re: Please validate the update [message #417356 is a reply to message #417353] Fri, 07 August 2009 10:17 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
where (aa.code,aa.id) in (select code, id from tmp)

Regards
Michel
Previous Topic: iterative field matching process
Next Topic: SQL CSV Spool with UNION ALL
Goto Forum:
  


Current Time: Sat Dec 10 15:05:45 CST 2016

Total time taken to generate the page: 0.08158 seconds