delete syntax

[kewldudehere]

I am performing deleting action but getting the error
Incorrect syntax near ','


delete from table1a
where (a.col1,a.col2,a.col3,a.col4) not in
(select b.col1,b.col2,b.col3,b.col4
from table2 b)

Here table 1 and table 2 are similar in structure but in diff databases and col1,col2,col3,col4 together form the primary key.

[iburyak]

Try this:


[PHP]
Delete from table1 a
where NOT EXISTS (select *
from table2 b
where b.col1 = a.col1 and b.col2 = a.col2 and b.col3 = a.col3 and b.col4 = a.col4)[/PHP]

[kewldudehere]

Hi i tried that code..but it is giving error at first line
Incorrect syntax near 'a'.

[iburyak]

Try to do this:

[PHP]
Delete from table1 from table1 a
where NOT EXISTS (select *
from table2 b
where b.col1 = a.col1 and b.col2 = a.col2 and b.col3 = a.col3 and b.col4 = a.col4)

[/PHP]

[iburyak]

I would suggest running below statement firs just to be sure correct records will be deleted if it is a production table.

[PHP]Select *
from table1 a
where NOT EXISTS (select *
from table2 b
where b.col1 = a.col1 and b.col2 = a.col2 and b.col3 = a.col3 and b.col4 = a.col4) [/PHP]

[kewldudehere]

Hi...it worked out...gr8

But the syntax sounds strange..Cam u explain me more abt this syntax


Delete from table1 from table1 a

[iburyak]

This is exact syntax from SQL help:

[PHP]DELETE
[ FROM ]
{ table_name WITH ( < table_hint_limited > [ ...n ] )
| view_name
| rowset_function_limited
}

[ FROM { < table_source > } [ ,...n ] ]

[ WHERE
{ < search_condition >
| { [ CURRENT OF
{ { [ GLOBAL ] cursor_name }
| cursor_variable_name
}
] }
}
]
[ OPTION ( < query_hint > [ ,...n ] ) ] [/PHP]

First “From” tells SQL from which table to delete and is optional.
Second “From” is used in cases when you need a join or alias for a table name that happened to be the same one as deleted table.

Example:
[PHP]DELETE titleauthor
FROM titleauthor INNER JOIN titles
ON titleauthor.title_id = titles.title_id
WHERE titles.title LIKE '%computers%'[/PHP]

Hope it helps. I am not very good with explanations .... :)

Irina.

[kewldudehere]

Thanx for ur time dude..

In fact u r very good at explanation....

[kewldudehere]

Hi,

Just like delete, i want to perform insert operation.
Delete from table1 from table1 a
where NOT EXISTS (select *
from table2 b
where b.col1 = a.col1 and b.col2 = a.col2 and b.col3 = a.col3 and b.col4 = a.col4)

I tried as but gives error at 'from'.What is the correct syntax

insert into table1 from table1 a
where NOT EXISTS (select *
from table2 b
where b.col1 = a.col1 and b.col2 = a.col2 and b.col3 = a.col3 and b.col4 = a.col4)

--------------------------------------------------------------------------------