pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedbest way to swap two records (computer details)
7+ messages / 4 participants
[nested] [flat]
* best way to swap two records (computer details)
@ 2005-03-18 13:25 Gary Stainburn <gary.stainburn@ringways.co.uk>
2005-03-18 16:32 ` Re: best way to swap two records (computer details) Greg Patnude <gpatnude@hotmail.com>
2005-03-18 19:54 ` Re: best way to swap two records (computer details) PFC <lists@boutiquenumerique.com>
0 siblings, 2 replies; 7+ messages in thread
From: Gary Stainburn @ 2005-03-18 13:25 UTC (permalink / raw)
To: pgsql-sql
Hi folks.
I have a table called pieces which contain every piece of hardware and
software within my company.
Each piece has an owner attribute which points to another piece which -
funnily enough - owns it.
For example records for CPU, motherboard, HDD, O/S, and applications
will all be owned by a piece record representing a computer.
I'm currently going through an upgrade process at the moment where I
build a new PC, install all relevent software and use Documents and
Settings Transfer Wizard to move a user onto the new PC before wiping
and disposing the old PC.
My question is what's the best way to swap settings between the two
computer records and swap any software installed? Ideally I'd like it
in the form of a function where I can pass the two p_id's and return a
boolean reflecting success (true) or fail (false).
Currently I do this manually with:
update pieces set p_name = 'LSALES1', p_location = 'Mike Haley', p_site
= 'L' where p_id = 724;
update pieces set p_name = 'SPARE', p_location = 'spare', p_site = 'L'
where p_id = 305;
update pieces set p_owner = 724 where p_owner = 305 and p_type in (
select hwt_id from hw_types where hwt_cat in (
select hwc_id from hw_categories where hwc_hwg_id = 7));
The hw_types and hw_categories select all O/S and application software.
This doesn't put any software currently on 305 onto 724 which would be
nice.
(I'm not after someone to do my work for me, but a good starting point
would be very much appreciated)
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: best way to swap two records (computer details)
2005-03-18 13:25 best way to swap two records (computer details) Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2005-03-18 16:32 ` Greg Patnude <gpatnude@hotmail.com>
2005-03-23 17:28 ` Re: best way to swap two records (computer details) Gary Stainburn <gary.stainburn@ringways.co.uk>
1 sibling, 1 reply; 7+ messages in thread
From: Greg Patnude @ 2005-03-18 16:32 UTC (permalink / raw)
To: pgsql-sql
How about a user defined function ???
CREATE OR REPLACE FUNCTION harwareupdate(integer, integer) RETURNS BOOLEAN
AS '
update pieces set p_name = \'LSALES1\', p_location = \'Mike Haley\',
p_site = \'L\' where p_id = $1;
update pieces set p_name = \'SPARE\', p_location = \'spare\', p_site =
\'L\'
where p_id = 2;
update pieces set p_owner = $1 where p_owner = $2 and p_type
in (select hwt_id from hw_types where hwt_cat in (
select hwc_id from hw_categories where hwc_hwg_id = 7
)
);
SELECT TRUE;
' LANGUAGE SQL;
Then all you need to do is:
SELECT * FROM updatehardware(724, 305);
"Gary Stainburn" <gary.stainburn@ringways.co.uk> wrote in message
news:200503181325.23293.gary.stainburn@ringways.co.uk...
> Hi folks.
>
> I have a table called pieces which contain every piece of hardware and
> software within my company.
>
> Each piece has an owner attribute which points to another piece which -
> funnily enough - owns it.
>
> For example records for CPU, motherboard, HDD, O/S, and applications
> will all be owned by a piece record representing a computer.
>
> I'm currently going through an upgrade process at the moment where I
> build a new PC, install all relevent software and use Documents and
> Settings Transfer Wizard to move a user onto the new PC before wiping
> and disposing the old PC.
>
> My question is what's the best way to swap settings between the two
> computer records and swap any software installed? Ideally I'd like it
> in the form of a function where I can pass the two p_id's and return a
> boolean reflecting success (true) or fail (false).
>
> Currently I do this manually with:
>
> update pieces set p_name = 'LSALES1', p_location = 'Mike Haley', p_site
> = 'L' where p_id = 724;
> update pieces set p_name = 'SPARE', p_location = 'spare', p_site = 'L'
> where p_id = 305;
>
> update pieces set p_owner = 724 where p_owner = 305 and p_type in (
> select hwt_id from hw_types where hwt_cat in (
> select hwc_id from hw_categories where hwc_hwg_id = 7));
>
> The hw_types and hw_categories select all O/S and application software.
> This doesn't put any software currently on 305 onto 724 which would be
> nice.
>
> (I'm not after someone to do my work for me, but a good starting point
> would be very much appreciated)
>
> --
> Gary Stainburn
>
> This email does not contain private or confidential material as it
> may be snooped on by interested government parties for unknown
> and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: best way to swap two records (computer details)
2005-03-18 13:25 best way to swap two records (computer details) Gary Stainburn <gary.stainburn@ringways.co.uk>
2005-03-18 16:32 ` Re: best way to swap two records (computer details) Greg Patnude <gpatnude@hotmail.com>
@ 2005-03-23 17:28 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2005-03-23 17:59 ` Re: best way to swap two records (computer details) Michael Long <mlong@datalong.com>
0 siblings, 1 reply; 7+ messages in thread
From: Gary Stainburn @ 2005-03-23 17:28 UTC (permalink / raw)
To: pgsql-sql
On Friday 18 March 2005 4:32 pm, you wrote:
> How about a user defined function ???
>
> CREATE OR REPLACE FUNCTION harwareupdate(integer, integer) RETURNS
> BOOLEAN AS '
>
> update pieces set p_name = \'LSALES1\', p_location = \'Mike
> Haley\', p_site = \'L\' where p_id = $1;
>
> update pieces set p_name = \'SPARE\', p_location = \'spare\',
> p_site = \'L\'
> where p_id = 2;
>
This isn't quite what I want. I want to SWAP the p_name and p_location
between the two records, not set them to static values.
Any help would be appreciated.
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: best way to swap two records (computer details)
2005-03-18 13:25 best way to swap two records (computer details) Gary Stainburn <gary.stainburn@ringways.co.uk>
2005-03-18 16:32 ` Re: best way to swap two records (computer details) Greg Patnude <gpatnude@hotmail.com>
2005-03-23 17:28 ` Re: best way to swap two records (computer details) Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2005-03-23 17:59 ` Michael Long <mlong@datalong.com>
0 siblings, 0 replies; 7+ messages in thread
From: Michael Long @ 2005-03-23 17:59 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
> On Friday 18 March 2005 4:32 pm, you wrote:
>> How about a user defined function ???
>>
>> CREATE OR REPLACE FUNCTION harwareupdate(integer, integer) RETURNS
>> BOOLEAN AS '
>>
>> update pieces set p_name = \'LSALES1\', p_location = \'Mike
>> Haley\', p_site = \'L\' where p_id = $1;
>>
>> update pieces set p_name = \'SPARE\', p_location = \'spare\',
>> p_site = \'L\'
>> where p_id = 2;
>>
>
Why not declare variables and select the current values into them and then update
the records with the appropriate values.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: best way to swap two records (computer details)
2005-03-18 13:25 best way to swap two records (computer details) Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2005-03-18 19:54 ` PFC <lists@boutiquenumerique.com>
2005-03-23 17:26 ` Re: best way to swap two records (computer details) Gary Stainburn <gary.stainburn@ringways.co.uk>
1 sibling, 1 reply; 7+ messages in thread
From: PFC @ 2005-03-18 19:54 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql
> My question is what's the best way to swap settings between the two
> computer records and swap any software installed? Ideally I'd like it
> in the form of a function where I can pass the two p_id's and return a
> boolean reflecting success (true) or fail (false).
I'd say something like that (generic table names) :
If you're confident :
UPDATE stuff SET owner = (CASE IF owner='A' THEN 'B' ELSE 'A' END) WHERE
owner IN ('A','B')
If you're paranoid :
UPDATE stuff SET owner = (CASE IF owner='A' THEN 'B' ELSE IF owner='B'
THEN 'A' ELSE owner END) WHERE owner IN ('A','B')
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: best way to swap two records (computer details)
2005-03-18 13:25 best way to swap two records (computer details) Gary Stainburn <gary.stainburn@ringways.co.uk>
2005-03-18 19:54 ` Re: best way to swap two records (computer details) PFC <lists@boutiquenumerique.com>
@ 2005-03-23 17:26 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2005-03-23 23:25 ` Re: best way to swap two records (computer details) Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 7+ messages in thread
From: Gary Stainburn @ 2005-03-23 17:26 UTC (permalink / raw)
To: pgsql-sql
On Friday 18 March 2005 7:54 pm, you wrote:
> > My question is what's the best way to swap settings between the two
> > computer records and swap any software installed? Ideally I'd like
> > it in the form of a function where I can pass the two p_id's and
> > return a boolean reflecting success (true) or fail (false).
>
> I'd say something like that (generic table names) :
>
> If you're confident :
> UPDATE stuff SET owner = (CASE IF owner='A' THEN 'B' ELSE 'A' END)
> WHERE owner IN ('A','B')
>
> If you're paranoid :
> UPDATE stuff SET owner = (CASE IF owner='A' THEN 'B' ELSE IF
> owner='B' THEN 'A' ELSE owner END) WHERE owner IN ('A','B')
Hello again.
I've tried the first one but get a syntax error. Anyone see why?
hardware=# update pieces set p_owner = (case if p_owner = 305 then 724
else 305 end) where p_owner in (305, 724);
ERROR: parser: parse error at or near "p_owner"
hardware=#
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: best way to swap two records (computer details)
2005-03-18 13:25 best way to swap two records (computer details) Gary Stainburn <gary.stainburn@ringways.co.uk>
2005-03-18 19:54 ` Re: best way to swap two records (computer details) PFC <lists@boutiquenumerique.com>
2005-03-23 17:26 ` Re: best way to swap two records (computer details) Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2005-03-23 23:25 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 0 replies; 7+ messages in thread
From: Gary Stainburn @ 2005-03-23 23:25 UTC (permalink / raw)
To: pgsql-sql
On Wednesday 23 March 2005 5:26 pm, you wrote:
> On Friday 18 March 2005 7:54 pm, you wrote:
> > > My question is what's the best way to swap settings between the
> > > two computer records and swap any software installed? Ideally
> > > I'd like it in the form of a function where I can pass the two
> > > p_id's and return a boolean reflecting success (true) or fail
> > > (false).
> >
> > I'd say something like that (generic table names) :
> >
> > If you're confident :
> > UPDATE stuff SET owner = (CASE IF owner='A' THEN 'B' ELSE 'A' END)
> > WHERE owner IN ('A','B')
> >
> > If you're paranoid :
> > UPDATE stuff SET owner = (CASE IF owner='A' THEN 'B' ELSE IF
> > owner='B' THEN 'A' ELSE owner END) WHERE owner IN ('A','B')
>
> Hello again.
>
> I've tried the first one but get a syntax error. Anyone see why?
>
> hardware=# update pieces set p_owner = (case if p_owner = 305 then
> 724 else 305 end) where p_owner in (305, 724);
> ERROR: parser: parse error at or near "p_owner"
> hardware=#
Sorted it. The 'IF' should be 'WHEN'
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-03-23 23:25 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-18 13:25 best way to swap two records (computer details) Gary Stainburn <gary.stainburn@ringways.co.uk>
2005-03-18 16:32 ` Greg Patnude <gpatnude@hotmail.com>
2005-03-23 17:28 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2005-03-23 17:59 ` Michael Long <mlong@datalong.com>
2005-03-18 19:54 ` PFC <lists@boutiquenumerique.com>
2005-03-23 17:26 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2005-03-23 23:25 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
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