Join & Update

Question

Join & Update

rshukis10

SSC Rookie

Points: 30
More actions
February 12, 2016 at 12:28 pm

#311427

Hello - I'm trying to write this formula in an Oracle database but...
I'm receiving this error
-- Failed: General SQL error. ORA-00933: SQL command not properly ended
Sorry if my coding structure isn't proper etiquette - still learning Current Code Picture
When the inventory is received that's what's called the "IN_DATE" So I recieved 20,000 plastic bottles today IN_DATE = GETNOW() or "2/12/2016" So this SQL will run EVERY DAY at 5pm or so so Everything that is RECEIVED that DAY that follows the Class listed at the bottom there is a field for each of these received items that is called "Non_Conformed_Allocatable" And this will be switched to "Y"
UPDATE FGMULTI
SET NON_CONFORM_ALLOCATABLE = 'Y'
FROM
fgmulti as fg
LEFT OUTER JOIN
arinvt as ar ON fg.arinvt_id = ar.id
WHERE
IN_Date = CurrentDate
AND
ar.Class LIKE 'CP%'
OR
ar.Class LIKE 'FG%'
OR
ar.Class LIKE 'IN%'
OR
ar.Class LIKE 'LA%'
OR
ar.Class LIKE 'PK%'

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Grumpy DBA SSChampion Points: 12368 More actions · Answer 1

For starters remove the "as", you don't need it when aliasing tables in Oracle. Give me a little time to mess with this, I'll be back.

UPDATE FGMULTI

SET NON_CONFORM_ALLOCATABLE = 'Y'

FROM

fgmulti fg

LEFT OUTER JOIN

arinvt ar ON fg.arinvt_id = ar.id

WHERE

IN_Date = CurrentDate

AND

ar.Class LIKE 'CP%'

OR

ar.Class LIKE 'FG%'

OR

ar.Class LIKE 'IN%'

OR

ar.Class LIKE 'LA%'

OR

ar.Class LIKE 'PK%'

Grumpy DBA SSChampion Points: 12368 More actions · Answer 2

To the best of my knowledge Oracle doesn't support Joins in an Update statement.

rshukis10 SSC Rookie Points: 30 More actions · Answer 3

Yes - just found this out as well

So now I'm trying to work with this:

UPDATE FGMULTI

JOIN

arinvt ar ON

LEFT OUTER JOIN

fgmulti.arinvt_id = ar.id

SET NON_CONFORM_ALLOCATABLE = 'Y'

WHERE

IN_Date = CurrentDate

AND

ar.Class LIKE 'CP%'

OR

ar.Class LIKE 'FG%'

OR

ar.Class LIKE 'IN%'

OR

ar.Class LIKE 'LA%'

OR

ar.Class LIKE 'PK%'

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 4

I disagree with Grumpy's first suggestion. Oracle may not "need" the AS for a table alias (and neither does SQL Server), but including it in my opinion greatly increases readability and maintainability of the code.

UPDATE ... FROM is a SQL Server only construction, not supported by the ANSI standard, or by most other DBMSes. (And for good reasons, it has some very dangerous and often overlooked effects).

The ANSI compliant way to write this kind of update would be:

UPDATE FGMULTI

SET NON_CONFORM_ALLOCATABLE = 'Y'

WHERE EXISTS

(SELECT *

FROM fgmulti as fg

LEFT OUTER JOIN

arinvt as ar ON fg.arinvt_id = ar.id

WHERE (something) = FGMULTI.(SomeColumn)

AND (other conditions));

I cannot provide more specific help then this. Your code lacks aliases for the columns (which in my opinion is a bad practice - the DBMS may know which column it is, but you have to spend time on it every time you look at the query). So I do not know which condition references which tables in your original code.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

rshukis10 (2/12/2016)
Yes - just found this out as well
So now I'm trying to work with this:
UPDATE FGMULTI
JOIN
arinvt ar ON
LEFT OUTER JOIN
fgmulti.arinvt_id = ar.id
SET NON_CONFORM_ALLOCATABLE = 'Y'
WHERE
IN_Date = CurrentDate
AND
ar.Class LIKE 'CP%'
OR
ar.Class LIKE 'FG%'
OR
ar.Class LIKE 'IN%'
OR
ar.Class LIKE 'LA%'
OR
ar.Class LIKE 'PK%'

Don't use UPDATE for joined updates in Oracle. Use MERGE instead. It's easier to use for updates.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

Hugo Kornelis (2/12/2016)
I disagree with Grumpy's first suggestion. Oracle may not "need" the AS for a table alias (and neither does SQL Server), but including it in my opinion greatly increases readability and maintainability of the code.

To each their own. I don't use "AS" for table aliases either. Come to think of it, I generally don't use "AS" for column aliases because I usually use the ColumnAlias = Expression form in my select lists. Like I said, to each their own. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 7

That may be an unrestricted update. The left-joined table contributes nothing, and the WHERE clause is an unpredictable mess of AND and OR. What exactly are you trying to do?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]