Inventory queries: HELP for sql noob

[bunglefoot]

Hi, I'm new to SQL (experienced in other types of programming, but no DB stuff) and have the following problem.I have a table coming in from a portable device and a table on the pc with one additional field that contains a numeric value. I want to do the following:

If a record is on the device that is not on the PC, the record is copied to the table on the PC and has the new field initialized to one.

If a record is on the device AND on the PC, the new field is incremented.

If a record is on the PC but not on the device, the record is deleted OR incremented and deleted, i have to look into that.

Before the record is deleted in the third step though, a report with all fields on the PC side database is printed out, sorted by one of the other fields.

Any and all help is appreciated, thanks people!

 

[JohnDTampaBay]

I assume there is an ID or NAME field in both tables that you can JOIN on to determine if a row is in both tables. It is hard to get specific without knowing all the columns involved, but this should point you in the right direction.

-- Update the rows that exist in both
UPDATE pc
SET NumColumn = = NumColumn + 1
FROM PCTable pc
   INNER JOIN DeviceTable d
       ON pc.ID = d.ID

-- Insert new rows from device
INSERT INTO PCTable
SELECT *, 1 AS NumColumn
FROM DeviceTable d
WHERE NOT EXISTS(SELECT * FROM PCTable pc
                 WHERE pc.ID = d.ID)

-- Select data for report
SELECT whatever FROM PCTable

-- Delete rows from PC not on the device
DELETE FROM PCTable
FROM PCTable pc
WHERE NOT EXISTS(SELECT * FROM DeviceTable d
                 WHERE d.ID = pc.ID)

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[maswien]

Assume the table for pc is tpc and the table for device is tdev.

tpc: rid, event, cnt
tdev: rid, event

Then you can achieve that by:

select case when tpc.rid is null then tdev.rid
else tpc.rid end as rid,
case when tpc.event is null then tdev.event
else tpc.event end as event,
case when tpc.cnt is null then 1
else cnt + 1 end as cnt
into tempTable
from tpc full outer join tdev on tpc.rid = tdev.rid and tpc.event = tdev.event
where tdev.rid is not null and tdev.event is not null



Look the tempTable, if the content is what you want in tpc , you can delete tpc and insert all the data from tempTable to tpc.