I want to update top 30% of a table. Is there a way without creating
a separate table?
create table temp_tableA (jobno varchar (20), formno varchar (20),
otherinfo varchar(20))
truncate table temp_tableA
insert into temp_tableA values ('1', '1', 'aaa')
insert into temp_tableA values ('1', '1', 'bbb')
insert into temp_tableA values ('1', '1', 'ccc')
insert into temp_tableA values ('1', '1', 'ddd')
insert into temp_tableA values ('1', '2', 'eee')
insert into temp_tableA values ('1', '2', 'fff')
insert into temp_tableA values ('1', '2', 'ggg')
insert into temp_tableA values ('1', '3', 'hhh')
insert into temp_tableA values ('1', '3', 'iii')
insert into temp_tableA values ('1', '3', 'jjj')
insert into temp_tableA values ('1', '4', 'kkk')
Now I want to update only top 30% of temp_tableA to formno 5.
When I ran this query:
UPDATE temp_tablea
SET formno = 5
WHERE exists (SELECT TOP 30 PERCENT *
FROM temp_tablea
)
It updated all the rows; is there a way to update without creating a
separate table? 1 12511
"Geetha" <ge******@hotmail.com> wrote in message
news:4b**************************@posting.google.c om... I want to update top 30% of a table. Is there a way without creating a separate table?
create table temp_tableA (jobno varchar (20), formno varchar (20), otherinfo varchar(20)) truncate table temp_tableA insert into temp_tableA values ('1', '1', 'aaa') insert into temp_tableA values ('1', '1', 'bbb') insert into temp_tableA values ('1', '1', 'ccc') insert into temp_tableA values ('1', '1', 'ddd') insert into temp_tableA values ('1', '2', 'eee') insert into temp_tableA values ('1', '2', 'fff') insert into temp_tableA values ('1', '2', 'ggg') insert into temp_tableA values ('1', '3', 'hhh') insert into temp_tableA values ('1', '3', 'iii') insert into temp_tableA values ('1', '3', 'jjj') insert into temp_tableA values ('1', '4', 'kkk')
Now I want to update only top 30% of temp_tableA to formno 5.
When I ran this query: UPDATE temp_tablea SET formno = 5 WHERE exists (SELECT TOP 30 PERCENT * FROM temp_tablea )
It updated all the rows; is there a way to update without creating a separate table?
How do you identify the 'top' 30%? You might be looking for something like
this, assuming that the 'otherinfo' column is used to order the data:
UPDATE temp_tablea
SET formno = 5
WHERE otherinfo IN
(SELECT TOP 30 PERCENT otherinfo
FROM temp_tablea
ORDER BY otherinfo)
If you don't have ORDER BY, then you will effectively get a random 30% of
the rows.
Simon This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Dave |
last post by:
I have 2 tables, one with names, and another with addresses, joined by their
CIVICID number (unique to the ADDRESSINFO table) in Oracle.
I need to update a field in the NAMEINFO table for a...
|
by: Lauren Quantrell |
last post by:
In VBA, I constructed the following to update all records in
tblmyTable with each records in tblmyTableTEMP having the same
UniqueID:
UPDATE
tblMyTable RIGHT JOIN tblMyTableTEMP ON...
|
by: VK |
last post by:
<http://www.jibbering.com/faq/#FAQ3_2>
The parts where update, replacement
or add-on is needed are in <update> tag.
3.2 What online resources are available?
Javascript FAQ sites, please...
|
by: Philip Boonzaaier |
last post by:
I want to be able to generate SQL statements that will go through a list of
data, effectively row by row, enquire on the database if this exists in the
selected table- If it exists, then the colums...
|
by: Shapper |
last post by:
Hello,
I have created 3 functions to insert, update and delete an Access
database record. The Insert and the Delete code are working fine.
The update is not. I checked and my database has all...
|
by: jaYPee |
last post by:
I have search a lot of thread in google newsgroup and read a lot of
articles but still i don't know how to update the dataset that has 3
tables.
my 3 tables looks like the 3 tables from...
|
by: Zorpiedoman |
last post by:
I keep getting a concurrency exception the second time I make a change and
attempt to update a dataadapter. It appears this is by design, so there must
be something I can do to avoid it.
...
|
by: Stephen Plotnick |
last post by:
I'm very new to VB.NET 2003
Here is what I have accomplished:
MainSelectForm - Selects an item
In a public class I pass a DataViewRow to
ItemInformation1 Form
ItemInformation2 Form
|
by: Michel Esber |
last post by:
Hi all,
DB2 V8 LUW FP 15
There is a table T (ID varchar (24), ABC timestamp). ID is PK.
Our application needs to frequently update T with a new value for ABC.
update T set ABC=? where ID...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |