Copy link to clipboard
Copied
Hi! I'm getting error on my query, I don't know why I got this error, been searching on the net and did not find any answer.
Has any of you been working with Oracle? This query runs fine on MSSQL:
<cfquery name="X" datasource="#Trim(arguments.DBSource)#">
DELETE EmpTbl
FROM EmpTbl, BioDataTbl
WHERE BioDataTbl.BioId = EmpTbl.EmpId
AND BioDataTbl.RegYear = EmpTbl.RegYear
AND BioDataTbl.Loc = EmpTbl.Loc
AND BioDataTbl.RegYear = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(arguments.HRYear)#">
AND BioDataTbl.Loc = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(session.groupLoc)#">
AND Status IN (<cfqueryparam cfsqltype="cf_sql_varchar" value="0">,<cfqueryparam cfsqltype="cf_sql_varchar" value="x">)
</cfquery>
The error I got was:
The web site you are accessing has experienced an unexpected error.
Please contact the website administrator.
The following information is meant for the website developer for debugging purposes. | ||||||||
Error Occurred While Processing Request | ||||||||
|
Copy link to clipboard
Copied
You can only specify one table in a delete query.
Copy link to clipboard
Copied
Plus you can't specify a column to delete from, it's just "DELETE FROM", not "DELETE EmpTbl FROM".
Copy link to clipboard
Copied
<cfquery name="X" datasource="#Trim(arguments.DBSource)#">
DELETE FROM EmpTbl
WHERE EmpTbl.EmpId IN
(
SELECT EmpTbl.EmpId
FROM EmpTbl, BioDataTbl
WHERE EmpTbl.EmpId = BioDataTbl.BioId
AND EmpTbl.RegYear = BioDataTbl.RegYear
AND EmpTbl.Loc = BioDataTbl.Loc
AND BioDataTbl.RegYear = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(arguments.HRYear)#">
AND BioDataTbl.Loc = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(session.groupLoc)#">
AND Status IN ('0','x')
)
</cfquery>