• Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

SQL help, structuring a delete.

minestrone

More fingers than teeth
Joined
Apr 24, 2008
Messages
17,844
Likes received
990
Thanks received
182
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
 
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.
 
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. :nerd
 
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
 
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.
 

Advertisers

Back
Top