0

Possible Duplicate:
Oracle - Update statement with inner join

In SQL Server Developer Edition, I want to update a column with its own value and variable from another table.

In table sa_tran_item, ref_no6 field is showing "NYN" and other table apo_rms_i_item_place, SORTIMENTSGRUPP_KOD field showing 10

I want to update table sa_tran_item, ref_no6 field as "NYN10"

My query

UPDATE sa_tran_item 
SET ref_no6 = "ref_no6" + "a.SORTIMENTSGRUPP_KOD" 
FROM apo_rms_i_item_place a, sa_tran_item 
WHERE a.varnummer in  (select item from item_master where item_number_type='MANL' and PRIMARY_REF_ITEM_IND = 'Y' and item_parent in 
(select  ITEM from sa_tran_item where  error_ind = 'Y' and tran_seq_no in 
(select tran_seq_no from sa_error where tran_seq_in ='49910349001'  error_code like 'ACG_NOT_FOUND') ));
Community
  • 1
  • 1
user1977026
  • 3
  • 1
  • 2

1 Answers1

0

In oralce the double pipe || is used to concatenate string values. You can update using

SET ref_no6 = ref_no6 || TO_CHAR(a.SORTIMENTSGRUPP_KOD)

if column SORTIMENTSGRUPP_KOD is integer type and 

SET ref_no6 = ref_no6 || a.SORTIMENTSGRUPP_KOD

if it's of type string/varchar2.

TechDo
  • 18,398
  • 3
  • 51
  • 64
  • Ok. I updated the statement.. buts still error is SQL Error: ORA-00921: unexpected end of SQL command – user1977026 Jan 14 '13 at 11:16
  • my sql now was UPDATE sa_tran_item SET ref_no6 = ref_no6||(select TO_CHAR (SORTIMENTSGRUPP_KOD) FROM apo_rms_i_item_place WHERE varnummer in (select item from item_master where item_number_type='MANL' and PRIMARY_REF_ITEM_IND = 'Y' and item_parent in (select ITEM from sa_tran_item where error_ind = 'Y' and tran_seq_no in (select tran_seq_no from sa_error where tran_seq_in ='49910349001' and error_code like 'ACG_NOT_FOUND') )); – user1977026 Jan 14 '13 at 11:17
  • datatype SORTIMENTSGRUPP_KOD NUMBER – user1977026 Jan 14 '13 at 11:23
  • okay... I think your query is not supported in oracle. U need `MERGE` in oracle to do inner join update like in `MS SQL`. Please refer the link for syntax. http://psoug.org/reference/merge.html – TechDo Jan 14 '13 at 11:27