advanced delete query sql

[gadjodilo77]

Hi,
I have two tables:

dev table:

dev_id volgnr type
1 B-05-1001 1(pc)
2 B-05-3001 3(mon)
3 B-05-3002 3(mon)
4 B-05-3003


kop table:

tabelid kop_pc kop_mon
1 1 2
2 1 3
3 1 4

I have a dev table which stands for all the device with there dev_id (primary key and auto increment) there number (volgnr) and the type (1=pc and 3=monitor).

I also have a kop table to be able to attach monitors to computers. As you can see monitor 2,3, and 4 are all attached to computer 1. (kop_mon is the dev_id of table dev an kop_pc is the dev_id of table dev).

With the following query it is possible to select the computers number (volgnr) and the numbers (volgnr) of the attached monitors:

select c.dev_volgnr as comp_nr
, m.dev_volgnr as mon_name
from dev as c
inner
join kop
on kop.kop_pc
= c.dev_id
inner
join dev as m
on m.dev_id = kop.kop_mon
where c.dev_id = #URL.dev_id#

(thank you for the code!!!!)

However What I now want to do is make a query so I can delete one monitor from the kop table without deleting the monitor from the dev table.

I made a page where I query the dev table to show the results for dev_id 1 (the computer). The attached devices are shown into a formfield.

With if / ifelse I select the right formfield and want to delete a monitor (kop_mon 4) from the kop table (I suppose by using the number (volgnr B-05-3003) in any way which is showing in the formfield (FORM.number_fld1)....

Could somebody explain me how I could do this? How would the query look like?

Thank you in advance.

Gr, Kabbi

 

[gadjodilo77]

Hmm

First of all, is it even possible?

gr,

 

[philhege]

When you query for the child records in kop, return the record ID and use that for the row value in your form (checkbox, whatever).

Say you have a checkbox named "DeleteItem". The value of this checkbox for your monitor would be 3 (the PK of the record).

Then, in your action page,

DELETE FROM kop
WHERE tabelid IN(#form.DeleteItem#)

(Of course, evaluate for the existence of form.DeleteItem since this is a checkbox.)

HTH,

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.

 

[gadjodilo77]

Hi,

I have one more question:
If you look at this code:

"
select c.dev_volgnr as comp_nr
, m.dev_volgnr as mon_name
from dev as c
inner
join kop
on kop.kop_pc
= c.dev_id
inner
join dev as m
on m.dev_id = kop.kop_mon
where c.dev_id = #URL.dev_id#
"

But If I have a form checkbox named DeleteItem. How could I delete only the record which I select? For instance there are three monitors attached to computer 1. I want to display them all in the page into a different formfield. If I show all results into a formfield/checkbox all the checkboxes would have the name FORM.DeleteItem but they have different values attached to them (the record id's).

However if I use a cfIF like:

<cfif IsDefined("FORM.DeleteItem") AND #FORM.DeleteItem# NEQ ""> DELETE FROM kop
WHERE tabelid IN(#form.DeleteItem#) </cfif>

Will this work? Image I select all or two the checkboxes (they all have the same name but a different record id in the value) Will it then delete all the selected records only from kop?

Thanks in advance.

Gr, kabbi

 

[gadjodilo77]

I tried it and unfortunately this doesn't work with the cfif.....

grt.

 

[gadjodilo77]

Hi I made this work, but I still have trouble with something else...

I used the following evaluation: (FORM.dev_gekdev_2 is the name of the device like B-05-3003 for instance)

<cfif IsDefined("FORM.dev_gekdev_2") AND #FORM.dev_gekdev_2# NEQ "" AND IsDefined("FORM.DeleteItem") AND #FORM.DeleteItem# NEQ "">

DELETE FROM kop WHERE kop.kop_mon IN (#FORM.DeleteItem#) AND kop.kop_pc= #FORM.dev_id#

this works fine for the selected items to delete them, but actually I want to update some data for the rest of the showed attached monitors for which I didn't choose to delete?

If I use this:

<cfif IsDefined("FORM.dev_gekdev_2") AND #FORM.dev_gekdev_2# NEQ "" AND IsDefined("FORM.DeleteItem") AND #FORM.DeleteItem# EQ "">

It does not do the job because it only does one action for one formfield.

How could I make it work so that the non selected attached devices are being upgraded (go to the same location as computer 1.... the deleted items will go to a special location).

Gr, kabbi

 

[philhege]

One way would be to create a hidden form field with all attached device primary keys. Evaluate these against the deleted items, and do updates on the remaining records.

If you want to update the records with data entered in the form, you'll have to identify each form field uniquely, like "description_23", where "23" is the primary key of the record. Then you'll have to parse the form field names to see which value goes with which record.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Eschewing obfuscation diurnally.

 

[gadjodilo77]

Thank you!

 

[gadjodilo77]

Hi,

If I understand, this means that the formfields cannot be made dynamically. The formfields have to be made in advance doing it this way isn't it?

Grt, Kabbi

 

[philhege]

That's right. Each field name will have to be constructed with the record's primary key being appended to the field name. They have to be made dynamically for this approach to work.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Eschewing obfuscation diurnally.

 

[gadjodilo77]

thank you for your answer!