Proc sql updating multiple columns

00000 - "%s: invalid identifier"update ( select old. WORKEXTENSION; But I was getting following error, by the Im on 10g R2 SQL Error: ORA-00904: "NEW"."WORKEXTENSION": invalid identifier 00904. New Value Else employee Table.address End FROM employee Table, Value Table where employee Table. New Value Else ET.lastname End, address = CASE When(ET.address ='N' and ID=30) THEN VT. But they don't try something quite as complex as this.New Value Else ET.address End FROM employee Table ET, Value Table VT where ET. What if i need to update a table using data from an external table? from TBL_VOTERS old, VOTERSWITHNUMBERS new where old. Dear Tom, You mentioned previously that we need to have the table we are updating from to have a unique or primary key constraint. I want to write SQL ( not PL/SQL ) to update transaction table from mapping table at one stroke. regards & thanks pjp update ( select old.old_account_number, new.new_account_number from old_table old, mapping_table new where old.old_account_number = new.new_account_number ) set old_account_number = new_account_number; disabling foreign keys during the operation and enabling them afterward. update ( select old.old_account_number, new.new_account_number from old_table old, mapping_table new where old.old_account_number = new.old_account_number ) set old_account_number = new_account_number; (ie in the subquery, match the old account numbers not try matching the old in the old table to the new in the mapping table) Of course if the original posting had the CREATEs/INSERTS etc, it would have a quick and east test.

New Value Else employee Table.first Name End, lastname = CASE When(employee Table.lastname='N' and ID=20) THEN Value Table.

I use this statement: update table1 set col1 = col2 col3 where col4 = 'OK' and exists (select distinct 1 from table2 where table1.col_x = table2.col_x2 and table2.col_y2 = 'super OK') This update works, but is too slow... September 26, 2008 - pm UTC you can use merge merge into t1 using t2 on (t1.x = t2.a) when matched then update set y = t2.b; if there exist duplicated by t2.a (more than one row with t2.a having the same value), the merge will fail at runtime when it encounters you trying to modify Y in a non-deterministic fashion. Can we use the WHERE clause in the merge statement?

Here you want to set a qty to a constant for some records - and you 'anti-join' to the other table, not join. create table types 2 (type varchar2(1), 3 description varchar2(50), 4 factor number(4)); ************************************************************************ Insert table: insert into types values ('A','Monthly','12') / insert into types values ('B','HOURLY','1796') / insert into types values ('G','Daily','224') / insert into types values ('H','Per Visit','') / insert into types values ('I','Per session','') / ******************************************************************** insert into class_test values ('0001','3,281.18','4,177.64') / insert into class_test values ('0002','3,779.27','4,820.00') / insert into class_test values ('0003','4,779.27','5,820.00') / insert into class_test values ('0004','5,779.27','6,820.00') / insert into class_test values ('0005','7,311.45,'10,402.64') / insert into class_test values ('0005','','20.83') / insert into class_test values ('0006','','14,528.60') / insert into class_test values ('0007','5,001.82','5,727.91') / ****************************************************************** insert into jobs values ('0001','CSI','A','','50131.68') / insert into jobs values ('0002','TTI','A','','200') / insert into jobs values ('0003','SSP','A','','') / insert into jobs values ('0004','CCC','A','','') / insert into jobs values ('0006','CIO','A','','') / insert into jobs values ('0007','CIO','A','','') / It should be update rows in JOBS table where rate_type = 'A', and ITEM_NO of JOBS table should be match with ITEM_NO of class_test table. Name Marital Status Mobile Phone Email I want to update table A (Name and Marital Status) using table B data, considering that ID matches in both tables, and using a designated ID (the use of Id may be optional, but for sake of simplicity lets assume we only want to update 1 row, but multiple columns at same time) Update table A set table A. Interestingly the Update that is suggested by you takes 12 hours and Update using the PL/SQL cursor style takes 8 hrs consistently on the live environment! thanks in advance June 04, 2008 - pm UTC create table has to be followed by indexing, grants, constraints and everything else - have you accounted for THAT. Unfortunately i cant get the tkprof as i dont have access to. if you were asked to pilot a plane with a blindfold on - how well do you think you would do. I cannot tell you how to make something more efficient if I do not know what is impeding the efficiency in the first place.

Secondly you have shown me to update table with "KEY PRESERVED" method. IT NEEDS THIS LOGICALLY regardless of the method used, so just add it (for if a row in OLD joins to more than one row in NEW -- you are hosed, you cannot update this data, therefore NEW has a unique/primary key, add it to facilitate the update) I had a similiar query and i tried applying to my tables.

If it is not possible can you show me how to write "GOOD" sql ? i.e, one column of a table should get updated from another column of another table.

proc sql updating multiple columns-78proc sql updating multiple columns-8proc sql updating multiple columns-25

It will be vastly faster and more understandable.i have two tables names DETAILS and STATUS DETALIS contains eid,ename,sal and STATUS contains eid,sal,updationdate.

Join our conversation (64 Comments).
Click Here To Leave Your Comment Proc sql updating multiple columns.

Comments:

Leave a Reply

Your email address will not be published. Required fields are marked *