pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUsing the IN predicate in an UPDATE...
6+ messages / 4 participants
[nested] [flat]
* Using the IN predicate in an UPDATE...
@ 1998-10-14 13:06 Thomas Good <tomg@admin.nrnet.org>
1 sibling, 1 reply; 6+ messages in thread
From: Thomas Good @ 1998-10-14 13:06 UTC (permalink / raw)
To: Herouth Maoz <herouth@oumail.openu.ac.il>; +Cc: pgsql-sql
Herouth,
This query takes 20 years (poetic licence invoked ;-).
Do you have a smarter way to accomplish this end:
UPDATE table1 SET id = 2
WHERE rec_num IN
( SELECT rec_num
FROM table1
WHERE id = 1
);
This is an attempt to cleanup some user error...I have an old
foxpro db that uses a char str as an index and this index has no
check constraints (like, making the index unique...ouch.)
The new pg db is performing nicely, now that it's live...and
housing 12 years worth of data. But I am saddled with quite a bit
of housekeeping - correcting anomalies that were part and parcel
of the original (foxpro) design. Or absence thereof.
Thanks much!
Tom
---------- Sisters of Charity Medical Center ----------
Department of Psychiatry
----
Thomas Good <tomg@q8.nrnet.org>
Coordinator, North Richmond C.M.H.C. Information Systems
75 Vanderbilt Ave, Quarters 8 Phone: 718-354-5528
Staten Island, NY 10304 Fax: 718-354-5056
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Using the IN predicate in an UPDATE...
@ 1998-10-14 14:42 Herouth Maoz <herouth@oumail.openu.ac.il>
1 sibling, 1 reply; 6+ messages in thread
From: Herouth Maoz @ 1998-10-14 14:42 UTC (permalink / raw)
To: Thomas Good <tomg@admin.nrnet.org>; +Cc: pgsql-sql
At 15:06 +0200 on 14/10/98, Thomas Good wrote:
> This query takes 20 years (poetic licence invoked ;-).
> Do you have a smarter way to accomplish this end:
>
> UPDATE table1 SET id = 2
> WHERE rec_num IN
> ( SELECT rec_num
> FROM table1
> WHERE id = 1
> );
>
> This is an attempt to cleanup some user error...I have an old
> foxpro db that uses a char str as an index and this index has no
> check constraints (like, making the index unique...ouch.)
> The new pg db is performing nicely, now that it's live...and
> housing 12 years worth of data. But I am saddled with quite a bit
> of housekeeping - correcting anomalies that were part and parcel
> of the original (foxpro) design. Or absence thereof.
I didn't know I became an SQL guru... I don't even have the proper version
of Postgres for subqueries...
It all depends on what you have in mind. First, is this an operation you
are likely to perform frequently, or is this a one-time fix you want to run
on your system?
Which fields are indexed? If the id field is indexed, it seems to me the
above query should not be *that* lengthy. In any case, an EXISTS query is
supposed to be more efficient. Especially if the only index is on rec_num.
Try this:
UPDATE table1 SET id = 2
WHERE EXISTS (
SELECT *
FROM table1 t1
WHERE t1.rec_num = table1.rec_num
AND id = 1
);
I hope the above table aliasing scopes rec_num correctly... I have no way
of trying it myself, because, as I said, I don't have 6.3 as yet.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] Re: Using the IN predicate in an UPDATE...
@ 1998-10-14 20:27 Gene Selkov Jr. <selkovjr@mcs.anl.gov>
parent: Herouth Maoz <herouth@oumail.openu.ac.il>
0 siblings, 0 replies; 6+ messages in thread
From: Gene Selkov Jr. @ 1998-10-14 20:27 UTC (permalink / raw)
To: Thomas Good <tomg@admin.nrnet.org>; +Cc: pgsql-sql
> This query takes 20 years (poetic licence invoked ;-).
> Do you have a smarter way to accomplish this end:
>
> UPDATE table1 SET id = 2
> WHERE rec_num IN
> ( SELECT rec_num
> FROM table1
> WHERE id = 1
> );
I think the urge to do everything you might need inside the server can be disabling. I would export the table to a file, fix it (with perl, sed, whatever) and import it back again.
--Gene
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] Using the IN predicate in an UPDATE...
@ 1998-10-15 03:21 Leslie Mikesell <les@Mcs.Net>
parent: Thomas Good <tomg@admin.nrnet.org>
0 siblings, 2 replies; 6+ messages in thread
From: Leslie Mikesell @ 1998-10-15 03:21 UTC (permalink / raw)
To: tomg@admin.nrnet.org; +Cc: herouth@oumail.openu.ac.il; pgsql-sql
According to Thomas Good:
> This query takes 20 years (poetic licence invoked ;-).
> Do you have a smarter way to accomplish this end:
>
> UPDATE table1 SET id = 2
> WHERE rec_num IN
> ( SELECT rec_num
> FROM table1
> WHERE id = 1
> );
Am I missing something here or is this the same as:
UPDATE table1 SET id = 2 WHERE id = 1;
??
Les Mikesell
les@mcs.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] Using the IN predicate in an UPDATE...
@ 1998-10-15 12:37 Thomas Good <tomg@admin.nrnet.org>
parent: Leslie Mikesell <les@Mcs.Net>
1 sibling, 0 replies; 6+ messages in thread
From: Thomas Good @ 1998-10-15 12:37 UTC (permalink / raw)
To: Leslie Mikesell <les@Mcs.Net>; +Cc: herouth@oumail.openu.ac.il; pgsql-sql
RE: optimizing this query... ;-)
> UPDATE table1 SET id = 2
> WHERE rec_num IN
> ( SELECT rec_num
> FROM table1
> WHERE id = 1
> );
1) Herouth, thanks...I aim to try this where I really need to do
table aliasing in an update (it will come up as I continue to port
the foxpro stuff...) I know how to do it in a SELECT but was
befuddled (per usual) re aliasing in an UPDATE. Thanks again.
> Try this:
>
> UPDATE table1 SET id = 2
> WHERE EXISTS (
> SELECT *
> FROM table1 t1
> WHERE t1.rec_num = table1.rec_num
> AND id = 1
> );
>
> I hope the above table aliasing scopes rec_num correctly... I have no way
> of trying it myself, because, as I said, I don't have 6.3 as yet.
>
> Herouth
2) Gene - you're my kind of guy...but I've been scolded so many times
by my perl buddies for being an incurable shell scripter that I really
do try to make SQL do the work whenever possible...thanks for the reply!
> I think the urge to do everything you might need inside the server
> can be disabling. I would export the table to a file, fix it
> (with perl, sed, whatever) and import it back again.
> --Gene
> Am I missing something here or is this the same as:
> UPDATE table1 SET id = 2 WHERE id = 1;
> ??
> Les Mikesell
> les@mcs.com
3) Les, What are ya, some kinda smarta**? Yeah, somebody was missing
something, but it weren't you... ;-)
BTW, I never did get UnixWare to work right re UUCP via TCP (sending).
But receiving is happening so my Linux boxes poll the UW box to grab
files and exchange the mail. Dodgy workaround but it is UnixWare, after
all...the original white elephant.
Thanks for your help (again...!)
Cheers,
Tom
---------- Sisters of Charity Medical Center ----------
Department of Psychiatry
----
Thomas Good <tomg@q8.nrnet.org>
Coordinator, North Richmond C.M.H.C. Information Systems
75 Vanderbilt Ave, Quarters 8 Phone: 718-354-5528
Staten Island, NY 10304 Fax: 718-354-5056
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] Using the IN predicate in an UPDATE...
@ 1998-10-18 09:24 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Leslie Mikesell <les@Mcs.Net>
1 sibling, 0 replies; 6+ messages in thread
From: Herouth Maoz @ 1998-10-18 09:24 UTC (permalink / raw)
To: Thomas Good <tomg@admin.nrnet.org>; Leslie Mikesell <les@Mcs.Net>; +Cc: pgsql-sql
At 14:37 +0200 on 15/10/98, Thomas Good wrote:
> > Am I missing something here or is this the same as:
> > UPDATE table1 SET id = 2 WHERE id = 1;
> > ??
> > Les Mikesell
> > les@mcs.com
>
> 3) Les, What are ya, some kinda smarta**? Yeah, somebody was missing
> something, but it weren't you... ;-)
Hmmm. Are you sure? The original query you presented had different
semantics. It meant "If the recnum has any occurence with id=1, change all
its occurences (even those with id=17) to id=2".
That was the reason why I suggested an "exists" query instead. The above is
only equivalent when each recnum has only one id.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~1998-10-18 09:24 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-10-14 13:06 ` Thomas Good <tomg@admin.nrnet.org>
1998-10-15 03:21 ` Leslie Mikesell <les@Mcs.Net>
1998-10-15 12:37 ` Thomas Good <tomg@admin.nrnet.org>
1998-10-18 09:24 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1998-10-14 14:42 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1998-10-14 20:27 ` Gene Selkov Jr. <selkovjr@mcs.anl.gov>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox