SQL help, structuring a delete.

[minestrone]

Anyone know how this could be done...

I have a table (table1) of name value pairs.

role_id & role_name

I also have a table (table2) of the same columns which is basically a data subset of table 1. I wish to remove all the entries contained in table2 from table 1.

As the primary key is over both colums I don't know how to do this. What I want to do is kind of like..

DELETE from
table1
WHERE
role_id , role value
IN
(SELECT
role_id , role value
FROM
table 2) ;

But obviously that does not work. The DB is mySQL

Cheers for any help

[thunderlizard]

now in Oracle, that would have worked (with a little reordering of brackets).

How about something nice and hacky like
where role_id || " & " || role_name in...
or whatever the concatenation operator is in MySql.

[minestrone]

I'll give that a blast when I get back on my laptop when I am on the train.

I head to the pub most days to work on plan B for an hour.

[MrMark]

Anyone know how this could be done...

I have a table (table1) of name value pairs.

role_id & role_name

I also have a table (table2) of the same columns which is basically a data subset of table 1. I wish to remove all the entries contained in table2 from table 1.

As the primary key is over both colums I don't know how to do this. What I want to do is kind of like..



But obviously that does not work. The DB is mySQL

Cheers for any help

Hi - I'm getting a bit rusty on this now, but I'm fairly sure that Mysql isn't hot at handling sub-queries performance wise. Better to write a delete using a join statement:

DELETE t1 FROM tbl_name t1, tbl_name t2
WHERE t1.roleID=t2.roleID
AND t1.roleName=t2.roleName

[Zippy]

Hi - I'm getting a bit rusty on this now, but I'm fairly sure that Mysql isn't hot at handling sub-queries performance wise. Better to write a delete using a join statement:

DELETE t1 FROM tbl_name t1, tbl_name t2
WHERE t1.roleID=t2.roleID
AND t1.roleName=t2.roleName

I've got nowt better to do so I looked it up (haven't done an mySQL for years).
There is a problem with using sub-queries and DELETE but you can use correlated sub-queries.

[minestrone]

Cheers, i'll give that a whirl.