Mutiple Where table in Update/Set

[DeShaun]

I am writing the current update SQL

I am trying to update a field but the where requirements are from multiple table. I have tried a join and ON statement, but the error I receive is missing SET keyword

update SHRTCKG
set
SHRTCKG_CREDIT_HOURS = '1',
SHRTCKG_HOURS_ATTEMPTED = '1'
where
SHRTCKG_TERM_CODE = '201809'
and SFRSTCR_PIDM = SHRTCKG_PIDM
and SFRSTCR_CRN = '91643'
and SHRTCKG_CREDIT_HOURS = '0',
and SHRTCKG_HOURS_ATTEMPTED = '0';

 

[Andrzejek]

If you run this:

[pre]
SELECT
SHRTCKG_CREDIT_HOURS ,
SHRTCKG_HOURS_ATTEMPTED
FROM SHRTCKG
where
SHRTCKG_TERM_CODE = '201809'
and SFRSTCR_PIDM = SHRTCKG_PIDM
and SFRSTCR_CRN = '91643'
and SHRTCKG_CREDIT_HOURS = '0',
and SHRTCKG_HOURS_ATTEMPTED = '0';
[/pre]
do you get the record(s) that you want to update?


---- Andy

There is a great need for a sarcasm font.

 

[DeShaun]

No. The from statement is missing a table SFRSTCR

 

[Andrzejek]

If I have problems creating UPDATE statement, I try first to create SELECT statement that gives me the record(s) I want to Update.


---- Andy

There is a great need for a sarcasm font.

 

[DeShaun]

Also, I need to remove the comma and SHRTCKG_CREDIT_HOURS = '0'[highlight yellow],[/highlight]

 

[DeShaun]

Once I correct the statement, I am able to accurately select the fields I am updating.

 

[Andrzejek]

---- Andy

There is a great need for a sarcasm font.

 

[DeShaun]

That doesn't resolve the issue I am having with update SQL that I am writing.

The problem is since there is no from statement in my update syntax the script does not acknowledge the SFRSTCR table when running the update process. I am receiving an invalid identifier error for SFRSTCR_CRN.

 

[Andrzejek]

Do you have a working SELECT statement retrieving correct record(s) to update?
If so, could you share it here?

Something like:[tt]
SELECT SHRTCKG_CREDIT_HOURS, SHRTCKG_HOURS_ATTEMPTED
FROM ...[/tt]


---- Andy

There is a great need for a sarcasm font.

 

[DeShaun]

SELECT
distinct SHRTCKG_CREDIT_HOURS , SHRTCKG_HOURS_ATTEMPTED, SHRTCKG_PIDM
FROM SHRTCKG,SHRTCKN
where
SHRTCKG_TERM_CODE = '201809'
and SHRTCKN_PIDM = SHRTCKG_PIDM
and SHRTCKN_CRN = '91643'
and SHRTCKG_CREDIT_HOURS = '1'
and SHRTCKG_HOURS_ATTEMPTED = '1';

 

[fredericofonseca]

a quick search in google... you know how to use it do you?...

https://stackoverflow.com/questions/2446764/update-statement-with-inner-join-on-oracle

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886