Issue Details (XML | Word | Printable)

Key: CORE-2288
Type: Improvement Improvement
Status: Closed Closed
Resolution: Won't Fix
Priority: Minor Minor
Assignee: Unassigned
Reporter: Eugenk Konkov
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

UPDATE/DELETE rows by filter on other table

Created: 24/Jan/09 04:26 PM   Updated: 14/Feb/11 12:09 PM
Component/s: Engine
Affects Version/s: None
Fix Version/s: None

Issue Links:
Replace
 


 Description  « Hide
create table A ( ID, name varchar(17) )
create table B (ID integer , parent_ID integer, name varchar(16) )

insert into A ( 1, 'a' )
insert into A ( 2, 'b' )
insert into A ( 3, 'c' )
insert into B ( 1, 1, 'a' )
insert into B ( 2, 1, 'b' )
insert into B ( 3, 1, 'c' )
insert into B ( 4, 2, 'd' )

EXAMPLE:
delete all rows from A which has no details:
delete from A
left join B on B.parent_ID = A.ID
where B.ID is null

update all rows in A which has details with name in ('c', 'd'):
update A
left join B on B.parent_ID = A.ID
set
 name = 'd'
where B.name = 'c' or B.name = 'd'


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Sean Leyne added a comment - 24/Jan/09 04:41 PM
The suggested syntax is not SQL standard compliant! Why should we implement it?

Adriano dos Santos Fernandes added a comment - 24/Jan/09 04:54 PM
For UPDATE, there is MERGE.

For DELETE, there is CORE-2005 that is about to extend MERGE too.

Eugenk Konkov added a comment - 24/Jan/09 06:21 PM
>The suggested syntax is not SQL standard compliant! Why should we implement it?
Because of it is handy. and PostgreSQL has implemented it

Dmitry Yemanov added a comment - 25/Jan/09 05:23 AM
UPDATE/DELETE with joins are ambiguous when the same table is used as both the operation target and inside the join clause. Because of that, many implementations (e.g. in MSSQL) has restrictions that makes this feature quite limited, IMHO. So I'd better go for the MERGE statement instead.