Delete Consult

[gatarossi]

Dear all,

I'm trying to do a DELETE consult, but I have a criteria from another
table:

DELETE stock.period, stock.type, estruture.estruture
FROM estruture INNER JOIN stock ON estruture.item_code =
stock.item_code
WHERE (((stock.period)="200709") AND ((stock.type)="FAT") AND
((estruture.estruture)="1"));

This code doesn´t work because access "sad" that I need to specify the
table that I need to exclude the data. In this case is the "stock"
table.

How can I change this code?

Thanks in advance!!!

André.

 

[Ken Snell \(MVP\)]

Are you actually wanting to delete records in both of the tables? Or are you
just wanting to "null" out fields' values?
--

Ken Snell
<MS ACCESS MVP>




Dear all,

I'm trying to do a DELETE consult, but I have a criteria from another
table:

DELETE stock.period, stock.type, estruture.estruture
FROM estruture INNER JOIN stock ON estruture.item_code =
stock.item_code
WHERE (((stock.period)="200709") AND ((stock.type)="FAT") AND
((estruture.estruture)="1"));

This code doesn´t work because access "sad" that I need to specify the
table that I need to exclude the data. In this case is the "stock"
table.

How can I change this code?

Thanks in advance!!!

André.

 

[gatarossi]

Dear Ken Snell,

I'm trying to delete all the data from the table stock. In this case I
need to delete some itens from this table, but I want to delete only
itens with a determinate condiction, and this condiction is in another
table.

Thanks a lot!!!

André.

 

[Ken Snell \(MVP\)]

OK - change your query's SQL statement to this:

DELETE stock.*
FROM stock
WHERE stock.item_code IN
(SELECT estruture.item_code
FROM estruture
WHERE estruture.estruture="1") AND
stock.period="200709" AND stock.type="FAT";

--

Ken Snell
<MS ACCESS MVP>





Dear Ken Snell,

I'm trying to delete all the data from the table stock. In this case I
need to delete some itens from this table, but I want to delete only
itens with a determinate condiction, and this condiction is in another
table.

Thanks a lot!!!

André.

 

[Pieter Wijnen]

This works faster (Jet is no good at IN, I think Allen Browne has a good
article on it)
DELETE
FROM stock
WHERE EXISTS (SELECT 'X' FROM
FROM estruture
WHERE estruture.estruture="1"
AND estruture.item_code = stock.item_code)
AND stock.period="200709"
AND stock.type="FAT"

Pieter

 

[Ken Snell \(MVP\)]

My experience is that Jet does pretty well with IN, but it really slows down
with NOT IN.

Your suggestion also will work for the OP.

--

Ken Snell
<MS ACCESS MVP>


"Pieter Wijnen"

 

[Pieter Wijnen]

No RDBMS works well with Not IN <g>
Seriously you have to include the field in the where clause for Jet to
"understand" the link properly, effectivly requiring a Exist join

Pieter