Update SQL with two tables in Oracle

Question

I have a sql like this

UPDATE A
SET A.TEMSILCI_KOD = 4
FROM S_MUSTERI A, S_TEKLIF B
WHERE A.TEMSILCI_KOD = 9
AND B.BAYI_KOD = 17
AND A.HESAP_NO = B.HESAP_NO

But i getting an error like this

Error starting at line 8 in command:
UPDATE A
SET A.TEMSILCI_KOD = 4
FROM S_MUSTERI A, S_TEKLIF B
WHERE A.TEMSILCI_KOD = 9
AND B.BAYI_KOD = 17
AND A.HESAP_NO = B.HESAP_NO
Error at Command Line:9 Column:22
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:

Where is the ERROR?

The multi-table UPDATE works on SQL Server, but not Oracle. — pascal
– pascal, Commented Feb 8, 2011 at 15:05

pascal · Accepted Answer · 2011-02-08 15:10:17Z

11

Maybe something like

UPDATE S_MUSTERI
SET TEMSILCI_KOD = 4
WHERE TEMSILCI_KOD = 9
AND EXISTS (SELECT 1 FROM S_TEKLIF B
WHERE S_MUSTERI.HESAP_NO = B.HESAP_NO
AND B.BAYI_KOD = 17)

answered Feb 8, 2011 at 15:10

pascal

3,3551 gold badge22 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

hkutluay · Accepted Answer · 2014-05-28 06:44:00Z

4

In Oracle the syntax to update a view is different from SQL*Server's syntax. In Oracle you could issue the following query:

UPDATE (SELECT A.TEMSILCI_KOD
          FROM S_MUSTERI A, S_TEKLIF B
         WHERE A.TEMSILCI_KOD = 9
           AND B.BAYI_KOD = 17
           AND A.HESAP_NO = B.HESAP_NO)
   SET TEMSILCI_KOD = 4

Note: This query will only work in Oracle if (S_TEKLIF.BAYI_KOD, S_TEKLIF.HESAP_NO) is unique (so that the update will not be ambiguous and each row from S_MUSTERI will be updated at most once).

edited May 28, 2014 at 6:44

hkutluay

6,9742 gold badges37 silver badges57 bronze badges

answered Feb 8, 2011 at 15:33

Vincent Malgrat

67.9k9 gold badges122 silver badges176 bronze badges

Comments

Doug Porter · Accepted Answer · 2011-02-08 15:06:48Z

2

Your update statement does not follow the correct syntax. There is no from clause in the update statement. It should follow the format

Update <table> 
   set <column> = <value> 
 where <conditions>

See this documentation on update: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10007.htm#i2067715

answered Feb 8, 2011 at 15:06

Doug Porter

7,9174 gold badges42 silver badges56 bronze badges

Collectives™ on Stack Overflow

Update SQL with two tables in Oracle

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related