|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/18/2009 10:51:12 AM
Posts: 6,
Visits: 36
|
|
Hi ,
This is Oracle query .
update dbname.sharp_app_usage_rpt_details A
set display_format = Coalesce ((select C.propvalue from dbname.sharp_app_usage_rpt_details B, dbname.sharp_properties C where B.request_ID = A.request_ID and B.report_Name = A.report_name and B.report_type = A.report_type and C.systemname = 'dfdf' and C.propname = B.report_name || '.' || B.report_type || '.OutputFmt' and C.PROPVALUE in ('dfd', 'dfda') ), display_format)
where request_id = 56546
Converted TD query
update table_a A FROM (select C.propvalue from
table_b B, table_c C ,table_a A
where B.request_ID = A.request_ID and B.report_Name = A.report_name and B.report_type = A.report_type
and C.systemname = 'dfdf' and C.propname = B.report_name || '.' || B.report_type || '.OutputFmt' and C.PROPVALUE in ('dfd', 'dfda') ) AS D(D1)
SET
display_format = D1
Coalesce (D1 , display_format )
where request_id = 56546
I am getting error as illegal usage of alias name
Thanks
Regards.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 3:55:54 AM
Posts: 111,
Visits: 263
|
|
Try:
update table_a A FROM (select C.propvalue from
table_b B, table_c C ,table_a A
where B.request_ID = A.request_ID and B.report_Name = A.report_name and B.report_type = A.report_type
and C.systemname = 'dfdf' and C.propname = B.report_name || '.' || B.report_type || '.OutputFmt' and C.PROPVALUE in ('dfd', 'dfda') ) AS D(D1)
SET
display_format = Coalesce (D1 , display_format )
----------------------------------------------
where request_id = 56546
;
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/18/2009 10:51:12 AM
Posts: 6,
Visits: 36
|
|
I got the query compiled without any errors but when I update I get the folowing error :
: [Teradata Database] [TeraJDBC 13.00.00.07] [Error 7547] [SQLState HY000] Target row updated by multiple source rows.
any help on this Please.
Regards.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 3:55:54 AM
Posts: 111,
Visits: 263
|
|
Apologies - you need to take the key details from your subquery and join them back to the main table.
Try:
update table_a FROM
(select A.Request_Id,
A.Report_Name,
A.Report_Type,
C.propvalue (D1)
from table_b B, table_c C ,table_a A
where B.request_ID = A.request_ID
and B.report_Name = A.report_name
and B.report_type = A.report_type
and C.systemname = 'dfdf'
and C.propname = B.report_name || '.' || B.report_type || '.OutputFmt'
and C.PROPVALUE in ('dfd', 'dfda')
and A.request_id = 56546 ) AS D
SET
display_format = Coalesce (D.D1 , Table_A.display_format )
where Table_A.request_id = D.Request_Id
and Table_A.Report_Name = D.Report_Name
And Table_A.Report_Type = D.Report_Type
;
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/18/2009 10:51:12 AM
Posts: 6,
Visits: 36
|
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/18/2009 10:51:12 AM
Posts: 6,
Visits: 36
|
|
I am getting the following error now .Can anybody help on this pelase
Message : [Teradata Database] [TeraJDBC 13.00.00.07] [Error 7547] [SQLState HY000] Target row updated by multiple source rows.
Thanks.
Regards.
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: Yesterday @ 12:42:08 AM
Posts: 74,
Visits: 32
|
|
Hi,
The issue you are seing is that 2 or more rows are appearing from your result set to update one target row. Effectively means that data row duplicates exist in your tables. Try this:
update table_a FROM
(select A.Request_Id,
A.Report_Name,
A.Report_Type,
C.propvalue (D1)
from table_b B, table_c C ,table_a A
where B.request_ID = A.request_ID
and B.report_Name = A.report_name
and B.report_type = A.report_type
and C.systemname = 'dfdf'
and C.propname = B.report_name || '.' || B.report_type || '.OutputFmt'
and C.PROPVALUE in ('dfd', 'dfda')
and A.request_id = 56546 ) AS D
SET
display_format = Coalesce (D.D1 , Table_A.display_format )
where Table_A.request_id = D.Request_Id
and Table_A.Report_Name = D.Report_Name
And Table_A.Report_Type = D.Report_Type
;
The section I have marked in BOLD above should be run seperately and checked whether multiple rows are being returned. If so, then simply GROUP BY to reduce this if possible, otherwise solve the problem with appropriate steps. Then your UPDATE will work fine.
Strive to success. Arun.
|
|
|
|