Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

update table column based on another column value 1

Status
Not open for further replies.
Topaz70

Topaz70

Technical User
Nov 7, 2006
5
I have statement need to update based on value st4.time_con_sdate using the value from st1.time_con_sdate and st4.time_con_edate with the value from st1.time_con_edate.




SELECT st1.time_treeid AS "room time id", st4.time_treeid AS "questionable time id", st1.time_handle, cass_code AS "case #", sn4.node_id AS "questionable node id", sn4.node_descrp, st1.time_con_sdate AS "room date", st4.time_con_sdate AS "questionable date"
FROM SYS_NODE sn1, SYS_NODE sn2, SYS_NODE sn3, SYS_NODE sn4, SYS_TIME st1, SYS_TIME st2, SYS_TIME st3, SYS_TIME st4, SYS_TREE str1, SYS_TREE str2, SYS_TREE str3, SYS_TREE str4, CASES
WHERE st1.time_treeid = str1.tree_id
AND st2.time_handle = st1.time_handle
AND st2.time_handle = st3.time_handle
AND st2.time_handle = st4.time_handle
AND sn1.node_id = 1130
AND sn2.node_id = 1132
AND sn3.node_id = 1136
AND str1.tree_nodeid = sn1.node_id
AND str2.tree_nodeid = sn2.node_id
AND str3.tree_nodeid = sn3.node_id
AND str4.tree_nodeid = sn4.node_id
AND st1.time_con_sdate = st2.time_con_sdate
AND st1.time_con_sdate = st3.time_con_sdate
AND st4.time_con_sdate <> st1.time_con_sdate
AND st4.time_con_sdate <> '01-Jan-1753'

Your help highly appreciated
 
Sort by date Sort by votes
Topaz,

provided you have primary keys, the following should do the trick.
Code: 
UPDATE (
		SELECT st1.time_treeid room_time_id,
		       st4.time_treeid questionable_time_id, 
			   st1.time_handle,
		       cass_code case_number, 
			   sn4.node_id AS "questionable node id",
		       sn4.node_descrp, 
			   st4.time_con_sdate AS "questionable date", 
			   st1.time_con_sdate AS "room date",
			   st4.time_con_edate, st1.time_con_edate
		  FROM sys_node sn1,
		       sys_node sn2,
		       sys_node sn3,
		       sys_node sn4,
		       sys_time st1,
		       sys_time st2,
		       sys_time st3,
		       sys_time st4,
		       sys_tree str1,
		       sys_tree str2,
		       sys_tree str3,
		       sys_tree str4,
		       cases
		 WHERE st1.time_treeid = str1.tree_id
		   AND st2.time_handle = st1.time_handle
		   AND st2.time_handle = st3.time_handle
		   AND st2.time_handle = st4.time_handle
		   AND sn1.node_id = 1130
		   AND sn2.node_id = 1132
		   AND sn3.node_id = 1136
		   AND str1.tree_nodeid = sn1.node_id
		   AND str2.tree_nodeid = sn2.node_id
		   AND str3.tree_nodeid = sn3.node_id
		   AND str4.tree_nodeid = sn4.node_id
		   AND st1.time_con_sdate = st2.time_con_sdate
		   AND st1.time_con_sdate = st3.time_con_sdate
		   AND st4.time_con_sdate <> st1.time_con_sdate
		   AND st4.time_con_sdate <> TO_DATE('01-Jan-1753','DD-Mon-YYYY')
        )
   SET st4.time_con_sdate = st1.time_con_sdate,
       st4.time_con_edate = st1.time_con_edate;

I did format this nicely, but the screen width may not show that.
I have added 'st4.time_con_edate, st1.time_con_edate' so that they can be updated.

Note also that I added a TO_DATE to your date value. Unless you can guarantee your date format, it's usually a good idea to state it explicitly.

You don't need to use the word 'AS' to alias in Oracle, it just happens. Normally, to avoid repeatedly having to use double quotes (") aliases are words joined with underscores. This is not essential, but it's usually deemed 'a good idea'.

In your query you also had a part which read:-
Code: 
cass_code AS "case #"

I have altered this because 'CASE' is a reserved word in Oracle, and so it should not normally be used in SQL. If you want to know what the Oracle reserved words are, then just
Code: 
SELECT * FROM V$RESERVED_WORDS;

Let us know how you get on.

Regards

Tharg

Grinding away at things Oracular
 
Upvote 0 Downvote
Thanks Tharg for your note. I found out there are no primay keys for the tables so is there an alternate way to do it.
 
Upvote 0 Downvote
Topaz,

yes, but it's a lot more long-winded. You have to use an 'exists' subquery.

The code below just updates st4, but you can use the general idea to achieve what you want.
Code: 
UPDATE st4
   SET time_con_sdate =
       (SELECT st1.time_con_sdate
          FROM sys_node sn1,
               sys_node sn2,
               sys_node sn3,
               sys_node sn4,
               sys_time st1,
               sys_time st2,
               sys_time st3,
               sys_time st4,
               sys_tree str1,
               sys_tree str2,
               sys_tree str3,
               sys_tree str4,
               cases
         WHERE st1.time_treeid = str1.tree_id
           AND st2.time_handle = st1.time_handle
           AND st2.time_handle = st3.time_handle
           AND st2.time_handle = st4.time_handle
           AND sn1.node_id = 1130
           AND sn2.node_id = 1132
           AND sn3.node_id = 1136
           AND str1.tree_nodeid = sn1.node_id
           AND str2.tree_nodeid = sn2.node_id
           AND str3.tree_nodeid = sn3.node_id
           AND str4.tree_nodeid = sn4.node_id
           AND st1.time_con_sdate = st2.time_con_sdate
           AND st1.time_con_sdate = st3.time_con_sdate
           AND st4.time_con_sdate <> st1.time_con_sdate
           AND st4.time_con_sdate <> TO_DATE('01-Jan-1753','DD-Mon-YYYY')
	     WHERE EXISTS
		      (SELECT 1
			     FROM sys_node sn1,
               sys_node sn2,
               sys_node sn3,
               sys_node sn4,
               sys_time st1,
               sys_time st2,
               sys_time st3,
               sys_time st4,
               sys_tree str1,
               sys_tree str2,
               sys_tree str3,
               sys_tree str4,
               cases
         WHERE st1.time_treeid = str1.tree_id
           AND st2.time_handle = st1.time_handle
           AND st2.time_handle = st3.time_handle
           AND st2.time_handle = st4.time_handle
           AND sn1.node_id = 1130
           AND sn2.node_id = 1132
           AND sn3.node_id = 1136
           AND str1.tree_nodeid = sn1.node_id
           AND str2.tree_nodeid = sn2.node_id
           AND str3.tree_nodeid = sn3.node_id
           AND str4.tree_nodeid = sn4.node_id
           AND st1.time_con_sdate = st2.time_con_sdate
           AND st1.time_con_sdate = st3.time_con_sdate
           AND st4.time_con_sdate <> st1.time_con_sdate
           AND st4.time_con_sdate <> TO_DATE('01-Jan-1753','DD-Mon-YYYY')
        );

I hope this helps. Please note that I have not tested the above code, as I have nothing against which to verify it.

Regards

Tharg





Grinding away at things Oracular
 
Upvote 0 Downvote
Hi Tharg

st4.time_con_sdate is feild do we need to change update to table name instead of field name
 
Upvote 0 Downvote
Topaz,

I thought st4 was a table name. If it's a field name, then yes, you need to change it to a table name.

The essence of the update is

update <tablename>
set <fieldname> = new value.

Without knowing the details of your tables, I can't be more informative than that.

Let us know how you get on.

Regards

T

Grinding away at things Oracular
 
Upvote 0 Downvote
This is the table in need to update will it work by below syntax
UPDATE sys_time
SET time_con_sdate =
(SELECT st1.time_con_sdate
FROM sys_node sn1,
sys_node sn2,
sys_node sn3,
sys_node sn4,
sys_time st1,
sys_time st2,
sys_time st3,
sys_time st4,
sys_tree str1,
sys_tree str2,
sys_tree str3,
sys_tree str4,
cases
WHERE st1.time_treeid = str1.tree_id
AND st2.time_handle = st1.time_handle
AND st2.time_handle = st3.time_handle
AND st2.time_handle = st4.time_handle
AND sn1.node_id = 1130
AND sn2.node_id = 1132
AND sn3.node_id = 1136
AND str1.tree_nodeid = sn1.node_id
AND str2.tree_nodeid = sn2.node_id
AND str3.tree_nodeid = sn3.node_id
AND str4.tree_nodeid = sn4.node_id
AND st1.time_con_sdate = st2.time_con_sdate
AND st1.time_con_sdate = st3.time_con_sdate
AND st4.time_con_sdate <> st1.time_con_sdate
AND st4.time_con_sdate <> TO_DATE('01-Jan-1753','mm-dd-YYYY')
AND st1.time_con_sdate > TO_DATE ('08-Sep-2006','mm-dd-YYYY')
AND st1.time_con_sdate < TO_DATE ( '12-Sep-2006', mm-dd-YYYY')
AND st1.TIME_CON_SDATE > TO_DATE ( '01-Oct-2006', mm-dd-YYYY')
AND st1.time_con_sdate < TO_DATE ( '12-Oct-2006' , mm-dd-YYYY')
AND st2.time_treeid = str2.tree_id
AND st3.time_treeid = str3.tree_id
AND st4.time_treeid = str4.tree_id
AND cass_id = st4.time_handle
WHERE EXISTS
(SELECT 1
FROM sys_node sn1,
sys_node sn2,
sys_node sn3,
sys_node sn4,
sys_time st1,
sys_time st2,
sys_time st3,
sys_time st4,
sys_tree str1,
sys_tree str2,
sys_tree str3,
sys_tree str4,
cases
WHERE st1.time_treeid = str1.tree_id
AND st2.time_handle = st1.time_handle
AND st2.time_handle = st3.time_handle
AND st2.time_handle = st4.time_handle
AND sn1.node_id = 1130
AND sn2.node_id = 1132
AND sn3.node_id = 1136
AND str1.tree_nodeid = sn1.node_id
AND str2.tree_nodeid = sn2.node_id
AND str3.tree_nodeid = sn3.node_id
AND str4.tree_nodeid = sn4.node_id
AND st1.time_con_sdate = st2.time_con_sdate
AND st1.time_con_sdate = st3.time_con_sdate
AND st4.time_con_sdate <> st1.time_con_sdate
AND st4.time_con_sdate <> TO_DATE('01-Jan-1753','mm-dd-YYYY')
AND st1.time_con_sdate > TO_DATE ('08-Sep-2006','mm-dd-YYYY')
AND st1.time_con_sdate < TO_DATE ( '12-Sep-2006', mm-dd-YYYY')
AND st1.TIME_CON_SDATE > TO_DATE ( '01-Oct-2006', mm-dd-YYYY')
AND st1.time_con_sdate < TO_DATE ( '12-Oct-2006' , mm-dd-YYYY')
AND st1.time_treeid = str1.tree_id
AND st2.time_treeid = str2.tree_id
AND st3.time_treeid = str3.tree_id
AND st4.time_treeid = str4.tree_id
AND cass_id = st4.time_handle
ORDER BY st4.time_handle;

);


 
Upvote 0 Downvote
Topaz,

you have the correct idea, the above looks fine.
Obviously there may be an error in the detail somewhere, but you have the essentials correct.

T

Grinding away at things Oracular
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Chopstik
  • Locked
  • Question
How to identify a package based on a code snippet 1
Replies
1
Views
709
Andrzejek
Andrzejek
TheLazyPig
  • Locked
  • Question
Separate 1 row value to multiple rows
Replies
4
Views
739
carp
carp
Andrzejek
  • Locked
  • Question
Show value from DATA_DEFAULT field 1
Replies
2
Views
881
Andrzejek
Andrzejek
TheLazyPig
  • Locked
  • Question
Inserting Text Decimal to Table Error
Replies
4
Views
781
carp
carp
Livia8
  • Locked
  • Question
SQL script to find number of characters allowed in &quot;Description&quot; column of &quot;AP_INVOIC 2
Replies
5
Views
323
Livia8
Livia8

Part and Inventory Search

Sponsor

Back
Top