Home » SQL & PL/SQL » SQL & PL/SQL » Update With Join
Update With Join [message #183371] Thu, 20 July 2006 10:13 Go to next message
griffin
Messages: 10
Registered: November 2005
Location: Holland
Junior Member
I know, ive searched...but cant realy find a answer wich works for me.

Ok. 2 tables.

Table1

ZIPCODE1
STREET1
NUMBER1

Table2

ZIPCODE2
STREET2
NUMBER2

I want to match the tables on
STREET1 = STREET2 and NUMBER1=NUMBER2

Then i want to update ZIPCODE2 with ZIPCODE1

I want to do this with a simple Update statement, i cant make it work in Oracle.

Does anyone have a sugestion?
it seems so simple....


Re: Update With Join [message #183374 is a reply to message #183371] Thu, 20 July 2006 10:38 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here it is:
update table2 t2 set
  t2.zipcode2 = (select t1.zipcode1
                 from table1 t1
                 where t1.street1 = t2.street2
                   and t1.number1 = t2.number2
                );
Re: Update With Join [message #183523 is a reply to message #183374] Fri, 21 July 2006 04:04 Go to previous messageGo to next message
griffin
Messages: 10
Registered: November 2005
Location: Holland
Junior Member
Already tried that...
Gives a ora-1427 error.
"single row sub query returns more than one row".

Extra info: Im working with Oracle 9.
Re: Update With Join [message #183569 is a reply to message #183371] Fri, 21 July 2006 07:09 Go to previous messageGo to next message
griffin
Messages: 10
Registered: November 2005
Location: Holland
Junior Member
3,..2,..1,... whooshhh...KICK!
Re: Update With Join [message #183573 is a reply to message #183569] Fri, 21 July 2006 07:15 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
And what happens if you add a DISTINCT in the inner select? The error is clear. Oracle expects one row in the subquery for each record of table2. You have rows in table2 that have more than one row in table1 (based on street and number). If these records show the same zipcode, a distinct can be a way out. If they are different, then you'll need to restrict your inner select a little bit more.

MHE
Re: Update With Join [message #183584 is a reply to message #183371] Fri, 21 July 2006 07:46 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


ONE MORE TIP :

Better to add where clause with Update
otherwise Unmatched zipcodes will be set to NULL ..

SQL> select ename,deptno,sal,comm from emp order by deptno;

ENAME          DEPTNO        SAL       COMM
---------- ---------- ---------- ----------
CLARK              10       2450        100
KING               10       5000        100
MILLER             10       1300        100
JONES              20       2975          0
FORD               20       3000          0
ADAMS              20       1100          0
SCOTT              20       3000          0
SMITH              20        800          0
ALLEN              30       1600        300
JAMES              30        950          0
TURNER             30       1500          0

ENAME          DEPTNO        SAL       COMM
---------- ---------- ---------- ----------
MARTIN             30       1250       1400
BLAKE              30       2850          0
WARD               30       1250        500

14 rows selected.

SQL> UPDATE emp e
  2  SET  comm =(SELECT e.comm*1.10 FROM dept d WHERE d.deptno = e.DEPTNO
  3                AND e.DEPTNO=30);

14 rows updated.

SQL> select ename,deptno,sal,comm from emp order by deptno;

ENAME          DEPTNO        SAL       COMM
---------- ---------- ---------- ----------
CLARK              10       2450
KING               10       5000
MILLER             10       1300
JONES              20       2975
FORD               20       3000
ADAMS              20       1100
SCOTT              20       3000
SMITH              20        800
ALLEN              30       1600        330
JAMES              30        950          0
TURNER             30       1500          0

ENAME          DEPTNO        SAL       COMM
---------- ---------- ---------- ----------
MARTIN             30       1250       1540
BLAKE              30       2850          0
WARD               30       1250        550

14 rows selected.

SQL>


Thumbs Up
Rajuvan.
Re: Update With Join [message #183587 is a reply to message #183584] Fri, 21 July 2006 08:01 Go to previous messageGo to next message
griffin
Messages: 10
Registered: November 2005
Location: Holland
Junior Member
Ok. I tried both tips, adding where and adding a distinct.
Still the same error.

Here's my real script

UPDATE BIS_LOCATIE B SET
B.POSTCODE = (SELECT DISTINCT A.PSTK 
	      FROM   demo_syncera.acn A
	      WHERE  LOWER(a.STRAATNAAM) = LOWER(B.STRAAT)
	      AND    a.HUISNUMMER = B.HUISNR)			 
WHERE b.POSTCODE IS NULL



But isnt the real problem the fact that the subquery returns more than one row? Instead of them not being unique rows.

The way i see it,..with this query im saying:
Update all rows with the one value you find as a result of the subquery. That off course is not what i want.
Am i making sense? Razz

[Updated on: Fri, 21 July 2006 08:16]

Report message to a moderator

Re: Update With Join [message #183591 is a reply to message #183587] Fri, 21 July 2006 08:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
No - you're using criteria from the row you're updating in the select statement that finds the value to update with.
Re: Update With Join [message #183593 is a reply to message #183587] Fri, 21 July 2006 08:23 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Adding Where Clause wont help in your proble ( Its just a tip ).

Problem lies where Maaher pointed at .
(More than One combination of (a.STRAATNAAM, a.HUISNUMMER ) in
demo_syncera.acn )

If you dont mind the DUPLICATION you can use MIN (A.PSTK )
instead of Distinct A.PSTK .(It Purely depends on ur actual requirement )


UPDATE BIS_LOCATIE B SET
B.POSTCODE = (SELECT MIN (A.PSTK )
	      FROM   demo_syncera.acn A
	      WHERE  LOWER(a.STRAATNAAM) = LOWER(B.STRAAT)
	      AND    a.HUISNUMMER = B.HUISNR)		
WHERE b.POSTCODE IS NULL


Quote:

NB: This will Update only the NULL POSTCODEs in BIS_LOCATIE
where there is match in demo_syncera.acn with MIN (PSTK)
It wont Update any existing POSTCODE with its matching
PSTK.

Thumbs Up
Rajuvan

[Updated on: Fri, 21 July 2006 08:24]

Report message to a moderator

Re: Update With Join [message #184847 is a reply to message #183593] Fri, 28 July 2006 05:37 Go to previous message
griffin
Messages: 10
Registered: November 2005
Location: Holland
Junior Member
Wow..it works THANKS!

Cant realy understand why because i would say that

WHERE  LOWER(a.STRAATNAAM) = LOWER(B.STRAAT)
AND    a.HUISNUMMER = B.HUISNR)	


would not work because b.straat is not known between the () but appearently it is.

It is kinda slow, but thats fine by me.
Previous Topic: Analyze table problem in oracle
Next Topic: Need Update Query
Goto Forum:
  


Current Time: Thu Dec 08 18:45:28 CST 2016

Total time taken to generate the page: 0.07364 seconds