| Subject | Re: [firebird-support] How to do a join delete with Flamerobin V 0.9.2.1851 |
|---|---|
| Author | Helen Borrie |
| Post date | 2013-03-20T00:06:07Z |
At 02:08 a.m. 20/03/2013, rosssmith55 wrote:
You say "in this instance I will be marking inventory items as deleted" so it really matters what you mean here. DELETE removes rows and you can't get them back. If you only want to mark them for deletion, e.g., by some later process, then you want an UPDATE statement, not DELETE.
For now, let's guess that you want to DELETE the rows from INVENTORY and INV_ID is the primary key or a unique constraint in INVENTORY:
DELETE FROM INVENTORY a1
WHERE
a1.INV_COUNTEDDATE < '01.10.2012, 00:00:00.001'
AND a1.inv_locationid = -1
AND EXISTS
(SELECT 1 FROM INVENTORY a
JOIN INVENTORY_SUPPLIER b
ON b.INVSUP_INVID = a.INV_ID
WHERE b.INVSUP_SUPPNUM = 10)
If it's the other thing you want, it's similar, except you presumably have some column in INVENTORY that you use to mark something as "deleted", sthg like INV_DELETED:
UPDATE INVENTORY a1
SET a1.INV_DELETED = 'Y'
WHERE
a1.INV_COUNTEDDATE < '01.10.2012, 00:00:00.001'
AND a1.inv_locationid = -1
AND EXISTS
(SELECT 1 FROM INVENTORY a
JOIN INVENTORY_SUPPLIER b
ON b.INVSUP_INVID = a.INV_ID
WHERE b.INVSUP_SUPPNUM = 10)
Helen Borrie, Support Consultant, IBPhoenix (Pacific)
Author of "The Firebird Book" and "The Firebird Book Second Edition"
http://www.ibphoenix.com/products/books/firebird_book
__________________________________________________________________
>Hello! I am fairly new to learning SQL, I am attempting to learn how to do a Join > Delete using flamerobin and was told this is a great place to learn good stuff :) Any way this is what i got so far.In SQL you can only delete rows from one table. In order to select which rows to delete, you provide a WHERE clause.
>
>SELECT a.INV_PARTNUMBER, a.INV_ALTPARTNUMBER, b.INVSUP_SUPPNUM, a.INV_DESCRIPTION1, a.INV_DESCRIPTION2, a.INV_LOCATIONID, a.INV_UOMID, a.INV_DEPTID, a.INV_STOCKONHAND, a.INV_COUNTEDDATE
>FROM INVENTORY a
>join INVENTORY_SUPPLIER b
>on a.INV_ID = b.INVSUP_INVID
>where a.INV_COUNTEDDATE < '01.10.2012, 00:00:00.001'
>and a.inv_locationid = -1
>and b.INVSUP_SUPPNUM = 10
>
>
>As you can see I have joined a few things (in this instance I will be marking inventory items as deleted) I just cant for the life of me figure out how to do it! I know how to just simply delete something but not in a join and for this it must be done in a join.
You say "in this instance I will be marking inventory items as deleted" so it really matters what you mean here. DELETE removes rows and you can't get them back. If you only want to mark them for deletion, e.g., by some later process, then you want an UPDATE statement, not DELETE.
For now, let's guess that you want to DELETE the rows from INVENTORY and INV_ID is the primary key or a unique constraint in INVENTORY:
DELETE FROM INVENTORY a1
WHERE
a1.INV_COUNTEDDATE < '01.10.2012, 00:00:00.001'
AND a1.inv_locationid = -1
AND EXISTS
(SELECT 1 FROM INVENTORY a
JOIN INVENTORY_SUPPLIER b
ON b.INVSUP_INVID = a.INV_ID
WHERE b.INVSUP_SUPPNUM = 10)
If it's the other thing you want, it's similar, except you presumably have some column in INVENTORY that you use to mark something as "deleted", sthg like INV_DELETED:
UPDATE INVENTORY a1
SET a1.INV_DELETED = 'Y'
WHERE
a1.INV_COUNTEDDATE < '01.10.2012, 00:00:00.001'
AND a1.inv_locationid = -1
AND EXISTS
(SELECT 1 FROM INVENTORY a
JOIN INVENTORY_SUPPLIER b
ON b.INVSUP_INVID = a.INV_ID
WHERE b.INVSUP_SUPPNUM = 10)
Helen Borrie, Support Consultant, IBPhoenix (Pacific)
Author of "The Firebird Book" and "The Firebird Book Second Edition"
http://www.ibphoenix.com/products/books/firebird_book
__________________________________________________________________