SQL DELETE WITH SUBQUERY SYNTAX NOT CHECKED

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL DELETE WITH SUBQUERY SYNTAX NOT CHECKED

Author

Topic

fprovoste
Starting Member

5 Posts

Posted - 2011-08-22 : 11:35:29

This is basic, sorry, had 2 tables

persons
----------
id_person int
nom_person varchar(255)

persons_friends
---------------
id_person int
id_friend int

i wrote a bad query, from visual studio 2005 (VB) like this

delete from persons_friends where id_person in
(
select id_persons from persons_friends
where id_friend=5
)

Didn't see i wrote a column name that didn't exists (id_persons) in subquery , so everytime this query delete all records from persons_friends, and not error of syntax was found :( .

So......what the hell is going on???? jejeje

Thanks

Kristen
Test

22859 Posts

Posted - 2011-08-22 : 11:56:42

[code]
select id_persons from persons_friends
where id_friend=5
[/code]
That code will return the id_persons values where id_friend=5.

Perhaps in your test data that is kinda everyone?, or everyone in the friends of the [persons] record you are testing on?

Either way, whoever is friends-with-person-5 will be deleted from the persons-friends table when you run that code.

Hopefully explains why-you-are-seeing-what-you-are-seeing?

"not error of syntax was found"

This is no error of syntax. The sub-query:

[code]
select id_persons from persons_friends
where id_friend=5
[/code]
will run just fine on its own (according to the schema description you provided

)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-22 : 11:59:41

do you mean


delete p
from persons p
join persons_friends pf
on pf.id_person =p.id_person
where pf.id_friend=5

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-22 : 12:01:03

That query returns a syntax error when I run it. Sure that's exactly the table definition and query you used?

Msg 207, Level 16, State 1, Line 11
Invalid column name 'id_persons'.

btw, no need for a subquery. You can just write this

delete from persons_friends where id_friend=5

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-22 : 12:02:29

quote:
Originally posted by Kristen

"not error of syntax was found"

This is no error of syntax. The sub-query:

select id_persons from persons_friends
where id_friend=5

will just just fine on its own (according to the schema description you provided )

Except that, in the schema given, there's no column id_persons in either table.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2011-08-22 : 12:04:46

OK, so there is a syntax error - I've read it more carefully now ... sorry about that.

Gail I expect the OP wants to delete a single row from persons_friends (ans is expecting that the IN will resolve to that single row). In which case I think a modification to Visakh's code is what is intended:


delete pf
from persons AS p
    join persons_friends AS pf
        on pf.id_person = p.id_person
where pf.id_friend=5

Kristen
Test

22859 Posts

Posted - 2011-08-22 : 12:06:21

Although that doesn't look right either ... that will delete the FRIEND record for everyone who is friends with pf.id_friend=5 ... and probably what is required is to delete the Friend record between "Current Person" and pf.id_friend=5

I'll stop guessing ...

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-22 : 12:11:40

I have no idea what he wants...

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2011-08-22 : 12:18:00

Turns out I only thought I did ...

fprovoste
Starting Member

5 Posts

Posted - 2011-08-22 : 12:31:30

Sorry, my delete clause is more complex than showed, i wanted to know why SQl server doesn't crush me when in Subquery Clause is a unknown column name, obviously my poor sql knowledge is workings against me, sooooo , back to study T-SQL

thanks everyone, and....can i close the topic?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-22 : 12:34:00

Probably what you have is a column in the subquery that, while not in any table within the subquery, is a valid column in a table in the outer query. That's perfectly valid. If we couldn't reference columns from the outer table within a subquery then correlated subqueries wouldn't work.

Qualify your column names with their table names always. You'll have much fewer surprises and much more robust code.

--
Gail Shaw
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-22 : 12:34:49

sorry but we still didnt get what caused your problem. would you mind to explain with example? certainly what you posted is not exactly what you're facing.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fprovoste
Starting Member

5 Posts

Posted - 2011-08-23 : 15:59:41

Exactly!!!! GilaMonster the field that is not in subquery, is a valid column in the outer query, that's why the sql server delete all rows in outer table :( . Thanks for help :)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-23 : 16:28:37

Yup. It's valid T-SQL and SQL Server did exactly what you told it to do.

Qualify your column names and you won't make such mistakes.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources