Re: best way to swap two records (computer details) - Mailing list 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
>