Can anyone help me convert this SQL to Oracle 8i SQL?

[matpj]

hi,
I have an update statement that I have been testing in Access on linked Oracle tables.
Pasting the statement straight into SQL Plus doesn't work.
I have tried playing around with it but it keeps saying 'missing SET statement on line 1'

here is the SQL:

UPDATE MSP_TEST_MSP_SYNCHTABLE INNER JOIN MSP_TEST_MSP_FLAG_FIELDS ON [MSP_TEST_MSP_SYNCHTABLE].[Proj_ID]=[MSP_TEST_MSP_FLAG_FIELDS].[Proj_ID]

SET MSP_TEST_MSP_SYNCHTABLE.Chargeable_Flag = [MSP_TEST_MSP_FLAG_FIELDS]![FLAG_VALUE]

WHERE ((([MSP_TEST_MSP_FLAG_FIELDS]![FLAG_FIELD_ID])=188743752)) And [MSP_TEST_MSP_FLAG_FIELDS].[FLAG_REF_UID]=0;


can anyone tell me how this might translate?

 

[PHV]

Something like this ?
UPDATE MSP_TEST_MSP_SYNCHTABLE
SET Chargeable_Flag=(SELECT FLAG_VALUE FROM MSP_TEST_MSP_FLAG_FIELDS WHERE Proj_ID=MSP_TEST_MSP_SYNCHTABLE.Proj_ID AND FLAG_FIELD_ID=188743752 AND FLAG_REF_UID=0)
WHERE Proj_ID IN (SELECT Proj_ID FROM MSP_TEST_MSP_FLAG_FIELDS WHERE FLAG_FIELD_ID=188743752 AND FLAG_REF_UID=0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BobAtHome]

Easier to understand if you use an alias for each table (A,B)


update MSP_TEST_MSP_SYNCHTABLE A
set A.Chargeable_Flag = (select B.FLAG_VALUE from MSP_TEST_MSP_FLAG_FIELDS B
where B.Proj_ID = A.Proj_ID
and B.FLAG_FIELD_ID = 188743752
and B.FLAG_REF_UID = 0);

The assumption here (since I don't know the full table structure and indices) is that the sub-query on the "MSP_TEST_MSP_FLAG_FIELDS" table will return only 1 row. In other words, each three field combination of Proj_ID + FLAG_FIELD_ID (188743752) + FLAG_REF_UID (0) in "MSP_TEST_MSP_FLAG_FIELDS" will be unique.

If it returns more than 1 row for any Proj_ID in "MSP_TEST_MSP_SYNCHTABLE", the sql will error out.

 

[matpj]

thanks Bob and PH
your assumption is correct.
thanks ofr your time - it was a great help.

 

[matpj]

hi folks, I managed to create the script and it works.
however, I have come across a script that I need in my process that for the life of me I cannot translate!

in MSP:

UPDATE MSP_TEST_MSP_SYNCHTABLE INNER JOIN MSP2003_C_CLIENTNAMES ON [MSP_TEST_MSP_SYNCHTABLE].[Client_code]=[MSP2003_C_CLIENTNAMES].[CLNTCODE] SET MSP_TEST_MSP_SYNCHTABLE.Client_Name = [MSP2003_C_CLIENTNAMES]![CLNTNAME];


this basically updates a field ni MSP_SYCNHTABLE with a field from C_CLIENTNAMES, using another field (Client_Code) in MSP_SYNCHTABLE to join with C_CLIENTNAMES.

I have tried converting this, but keep getting a message complaining of 'invalid column name' (i've checked all spellings over and over)
any ideas?