Problem with Decode

I having issues with this decode and I dont understand why?

Thank you
update IMAGE
SET IMAGEASCID = DECODE(NEW_ID, NULL, IMAGEASCID, NEW_ID)
FROM TEMP
WHERE IMAGE_CD = 'Template'
AND IMAGEASCID = PRJOLDID;
COMMIT;

Error starting at line 1 in command:
update IMAGE
SET IMAGEASCID = DECODE(NEW_ID, NULL, IMAGEASCID, NEW_ID)
FROM TEMP
WHERE IMAGETYCD = 'Template'
AND IMAGEASCID = PRJOLDID
Error at Command Line:2 Column:61
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:

Open in new window

lulubell-bAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
it's not the DECODE

there is no FROM clause

in an UPDATE statement
0
slightwv (䄆 Netminder) Commented:
You don't UPDATE FROM.

Do you mean:
update IMAGE
SET IMAGEASCID = SELECT DECODE(NEW_ID, NULL, IMAGEASCID, NEW_ID)
FROM TEMP
WHERE IMAGE_CD = 'Template'
AND IMAGEASCID = PRJOLDID;
COMMIT;


Be careful here because it will UPDATE ALL rows since the where clause is on the SELECT not the UPDATE.
0
lulubell-bAuthor Commented:
do I place a where exists then?


update IMAGE I
SET IMAGEASCID = (SELECT DECODE(T.NEW_ID, NULL, I.IMAGEASCID, T.NEW_ID)
FROM TEMP T
WHERE I.IMAGE_CD = 'Template')
where exists (select * from TEMP T
I.IMAGEASCID = T.PRJOLDID;
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

awking00Information Technology SpecialistCommented:
Besides the update/from issue, seems like a better function would be -
nvl(new_id, imagescid)
0
lulubell-bAuthor Commented:
update IMAGE I
SET IMAGEASCID = DECODE(T.NEW_ID, NULL, I.IMAGEASCID, T.NEW_ID)
FROM TEMP T
WHERE I.IMAGE_CD = 'Template')
where exists (select * from TEMP T
I.IMAGEASCID = T.PRJOLDID;
0
slightwv (䄆 Netminder) Commented:
You are back to the original syntax?  "SET IMAGEASCID = DECODE(T.NEW"

There is no "update from".

Can you provide the table definitions, some sample data and expected results?
0
lulubell-bAuthor Commented:
I want to find records from IMAGE where IMAGE_CD = 'Template' and update the IMAGEASCID with either the existing ID or the new ID


CREATE
  TABLE "CDB"."IMAGE"
  (
    "IMAGEID"       NUMBER NOT NULL ENABLE,
   "IMAGEASCID" NUMBER,
    "IMAGE_CD"  VARCHAR2(15 BYTE),
    "IMAGE_TX" VARCHAR2(15 BYTE),
    "IMAGE_SZ"   NUMBER)

CREATE
  TABLE "CDB"."TEMP"
  (
    "PRJOLDID" NUMBER,
    "NEW_ID"     NUMBER,
    "PID"     NUMBER,
    "STATUS"     VARCHAR2(1 BYTE)
  )
0
slightwv (䄆 Netminder) Commented:
Thanks for the table.

Now that I'm looking at it the requirement seems strange to me.  You want to update IMAGEASCID to a 'new' value based on a PRJOLDID=IMAGEASCID from the TEMP table?

I'm not following the relationship between the tables.

Can you provide some sample data and expected results?
0
lulubell-bAuthor Commented:
TEMP

PRJOLDID                  NEW_ID
----------------------------------------
1234514462      10547            
1234514572      10548            
10111            null


10547

IMAGEID                  IMAGE_CD                     IMAGEASCID
------------------------------------------------------------------------
1                               Template                      1234514462


AFTER UPDATE.......
10547

IMAGEID                  IMAGE_CD                     IMAGEASCID
------------------------------------------------------------------------
1                               Template                      10547


      
0
slightwv (䄆 Netminder) Commented:
See if this works.  I'm still not 100% sure.

update image i
set imageascid= (select new_id from temp t where new_id is not null and i.imageascid=t.prjoldid)
where exists (select 1 from temp t where i.image_cd='Template' and i.imageascid=t.prjoldid);

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lulubell-bAuthor Commented:
This worked perfectly. :) nice work around

Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.