Thread: best way to swap two records (computer details)

best way to swap two records (computer details)

From

Gary Stainburn

Date:

18 March 2005, 16:31:51

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

Re: best way to swap two records (computer details)

From

"Greg Patnude"

Date:

18 March 2005, 19:30:08

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
>

Re: best way to swap two records (computer details)

From

PFC

Date:

18 March 2005, 22:54:53

> 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')

Re: best way to swap two records (computer details)

From

Gary Stainburn

Date:

23 March 2005, 20:32:26

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

Re: best way to swap two records (computer details)

From

Gary Stainburn

Date:

23 March 2005, 20:35:19

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

Re: best way to swap two records (computer details)

From

"Michael Long"

Date:

23 March 2005, 20:59:58

> 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.

Re: best way to swap two records (computer details)

From

Gary Stainburn

Date:

24 March 2005, 02:31:30

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

unsubscribe

From

Alexander Vlasenko

Date:

24 April 2005, 16:41:53

unsubscribe