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?

Bedelia

The multi-table UPDATE works on SQL Server, but not Oracle. – Indescribable

are you doing this in a procedure? – Untrimmed

RTFM, there's no FROM. – Indescribable

Answer 1

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)

Indescribable

Answer 2

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).

Kreiner

Answer 3

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

Rarebit