Help on delete statement

stian1 · #1 (**permalink**) 06-04-07, 17:14

Hi,
I have two tables.
Table1:
MySymbol, BloombergSymbol
A, A_Bloomberg
B, B_Bloomberg

Table2:
MySymbol, Open, High, Low, Close
A,...
A,...
B,...
B,...

I want to perform one task--Delete all the records in table2 whose MySymbol matches one given BloombergSymbol in table1.

3x.

r937 · #2 (**permalink**) 06-04-07, 20:21

which dbms is this?

ashish_mat1979 · #3 (**permalink**) 06-05-07, 03:05

Try this query:

Code:

Delete Table2.* from Table1 LEFT JOIN Table2 ON Table1.MySymbol = Table2.MySymbol

aschk · #4 (**permalink**) 06-06-07, 03:44

Alternate syntax :

Code:

DELETE FROM table2 WHERE MySymbol IN (SELECT MySymbol FROM table1)

n.b. I have based this on MySQL syntax which may or may not work in your DB.

stian1 · #5 (**permalink**) 06-06-07, 10:04

sql server

Quote:

Originally Posted by r937

which dbms is this?

stian1 · #6 (**permalink**) 06-06-07, 10:06

this one works.
thank you guys.

Quote:

Originally Posted by aschk

Alternate syntax :

Code:

DELETE FROM table2 WHERE MySymbol IN (SELECT MySymbol FROM table1)

n.b. I have based this on MySQL syntax which may or may not work in your DB.

blindman · #7 (**permalink**) 06-06-07, 13:28

For SQL Server use:

DELETE table2
FROM table2
INNER JOIN table1 ontable2.MySymbol = table1.MySymbol

Internet Services

Web Development

Digital Marketing

Consumer Tech