Home » SQL & PL/SQL » SQL & PL/SQL » about sql update
about sql update [message #381111] Thu, 15 January 2009 05:35 Go to next message
ora_baby
Messages: 89
Registered: May 2008
Member
good day

i have table T1(col1 number, col2 number, col3 varchar2)

T1
-------
1 0 monday
2 0 friday

i make UPDATE

update T1
set col2 = col1
and col1 = 0

how will make update:
1. col2 = col1
2. col1 = 0
????

how work update in this case?
may be, better:

update T1
set col2 = (select col1 from T1 where col1 = 1)
and col1 = 0


any idea??????
Re: about sql update [message #381112 is a reply to message #381111] Thu, 15 January 2009 05:37 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Just as you've said:
UPDATE t1 SET
  col2 = col1,
  col1 = 0;
Re: about sql update [message #381113 is a reply to message #381111] Thu, 15 January 2009 05:39 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
how work UPDATE in this case?
Re: about sql update [message #381114 is a reply to message #381113] Thu, 15 January 2009 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It works well.
There is no order, ALL fields are updated at the same time.

Regards
Michel
Re: about sql update [message #381120 is a reply to message #381111] Thu, 15 January 2009 05:54 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
algorithm of update is interested for me
Re: about sql update [message #381128 is a reply to message #381120] Thu, 15 January 2009 06:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which algorithm?
- get the blocks/rows
- update the blocks/rows

Regards
Michel
Re: about sql update [message #381132 is a reply to message #381111] Thu, 15 January 2009 06:26 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
algorithm of update statement, update rows.
order of action?
Re: about sql update [message #381138 is a reply to message #381132] Thu, 15 January 2009 06:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
- get the blocks/rows
- update the blocks/rows

Regards
Michel
Re: about sql update [message #381144 is a reply to message #381111] Thu, 15 January 2009 07:37 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
update T1
set col2 = (select col1 from T1 where col1 = 1)
and col1 = 0

Just a description of algorithm of the UPDATE statement you posted in your initial post:
- take the table specified after UPDATE word (T1)
- take the rows conforming the WHERE condition (as there is none, all rows are taken)
- set the values of columns on the left side of '=' in the SET clause (COL2, COL1) to the expression values on the right side of '=' (result of the SELECT statement, 0)

The SELECT statement mentioned above may return following resultset:
- zero rows - then COL2 is updated to NULL
- one row with value 1 - then COL2 is updated to 1
- two or more rows with value 1 - then exception "ORA-01427: single-row subquery returns more than one row" is raised and no update is done

Maybe you should rather describe in words what you are trying to achieve and why Littlefoot's code does not satisfy it.

Also studying UPDATE Statement syntax and semantics would be good for you. It is described in SQL Reference book, available with all Oracle documentation books e.g. online on http://tahiti.oracle.com/.

[Edit: And I should do it too, as there shall be comma or WHERE word instead of AND]

[Updated on: Thu, 15 January 2009 18:18]

Report message to a moderator

Re: about sql update [message #381712 is a reply to message #381144] Mon, 19 January 2009 06:48 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
ok
how are rows uptaded in accent on oracle buffer, cash and other.
where stores data before update table - cash?
Re: about sql update [message #381714 is a reply to message #381712] Mon, 19 January 2009 06:55 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
cash? Oh! cache!
In short, Oracle reads blocks from disk to cache, modify blocks in cache, then write them to disk.
Read Database Concepts

Regards
Michel
Previous Topic: ERROR PLS-00428
Next Topic: A challenging question
Goto Forum:
  


Current Time: Sun Dec 04 14:58:10 CST 2016

Total time taken to generate the page: 0.04797 seconds