Author Topic: SQL Query help  (Read 3024 times)

Offline Alexsei

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2011
  • Posts: 1953
  • Total likes: 14
  • DansDeals.com Hat Tips 1
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Champlain NY, with 1' in canada
SQL Query help
« on: December 26, 2014, 09:48:43 AM »
Hi, any SQL experts here?
Looking to accomplish the following:

Delete all rows from table that do not have any 1 of a (list of number comma separated) in a specific column
TIA
Слава Україні! Героям слава!

Offline AnonymousUser

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Feb 2013
  • Posts: 2924
  • Total likes: 1
  • DansDeals.com Hat Tips 0
    • View Profile
Re: SQL Query help
« Reply #1 on: December 26, 2014, 09:53:36 AM »
Hi, any SQL experts here?
Looking to accomplish the following:

Delete all rows from table that do not have any 1 of a (list of number comma separated) in a specific column
TIA
How about:
DELETE FROM table_name WHERE column_name NOT IN (list);

Offline Alexsei

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2011
  • Posts: 1953
  • Total likes: 14
  • DansDeals.com Hat Tips 1
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Champlain NY, with 1' in canada
Re: SQL Query help
« Reply #2 on: December 26, 2014, 09:54:47 AM »
How about:
DELETE FROM table_name WHERE column_name NOT IN (list);
Hey AnonymousUser back to the rescue again  ;)

So format should be
DELETE FROM table_name WHERE column_name NOT IN (1134,2552,125,5454);
Слава Україні! Героям слава!

Offline AnonymousUser

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Feb 2013
  • Posts: 2924
  • Total likes: 1
  • DansDeals.com Hat Tips 0
    • View Profile
Re: SQL Query help
« Reply #3 on: December 26, 2014, 09:56:51 AM »
Hey AnonymousUser back to the rescue again  ;)
;)
So format should be
DELETE FROM table_name WHERE column_name NOT IN (1134,2552,125,5454);
I'm a bit rusty, but from a quick googling I believe that is correct.

Offline Alexsei

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2011
  • Posts: 1953
  • Total likes: 14
  • DansDeals.com Hat Tips 1
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Champlain NY, with 1' in canada
Re: SQL Query help
« Reply #4 on: December 26, 2014, 09:58:42 AM »
To explain more what i need,

I have various tables containing order info with 1 column orders_id containing the order#, then i have a list of order numbers and want all rows that the orders_id is not in that list to be dropped

Update it worked like a charm!
« Last Edit: December 26, 2014, 10:02:37 AM by Alexsei »
Слава Україні! Героям слава!

Offline AnonymousUser

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Feb 2013
  • Posts: 2924
  • Total likes: 1
  • DansDeals.com Hat Tips 0
    • View Profile
Re: SQL Query help
« Reply #5 on: December 26, 2014, 10:03:39 AM »
To explain more what i need,

I have various tables containing order info with 1 column orders_id containing the order#, then i have a list of order numbers and want all rows that the orders_id is not in that list to be dropped

Update it worked like a charm!
This should work, then.

Offline jl6647

  • Dansdeals Silver Elite
  • **
  • Join Date: Feb 2013
  • Posts: 84
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: SQL Query help
« Reply #6 on: December 26, 2014, 10:07:16 AM »
I would recommend the following

BEGIN TRAN
   DELETE table_1
   WHERE orders_id  NOT IN (SELECT
         orders_id
      FROM Table_2)

and then you do a test and use "commit tran" or "rollback tran"

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 8142
  • Total likes: 91
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: SQL Query help
« Reply #7 on: December 26, 2014, 10:07:49 AM »
You can always test out SQL like this by doing SELECT instead of DELETE, and reviewing the records to make sure you're deleting the right ones.
Workflowy. You won't know what you're missing until you try it.

Offline AnonymousUser

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Feb 2013
  • Posts: 2924
  • Total likes: 1
  • DansDeals.com Hat Tips 0
    • View Profile
Re: SQL Query help
« Reply #8 on: December 26, 2014, 10:09:36 AM »
I would recommend the following

BEGIN TRAN
   DELETE table_1
   WHERE orders_id  NOT IN (SELECT
         orders_id
      FROM Table_2)

and then you do a test and use "commit tran" or "rollback tran"
I don't know enough about transactions, but that sounds like a good idea.
If it's easier, instead of the subquery, you can put a comma separated list like above.

Offline jl6647

  • Dansdeals Silver Elite
  • **
  • Join Date: Feb 2013
  • Posts: 84
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: SQL Query help
« Reply #9 on: December 26, 2014, 10:14:19 AM »
I don't know enough about transactions, but that sounds like a good idea.
If it's easier, instead of the subquery, you can put a comma separated list like above.
transaction is fairly simple, it is good to be used when making any changes in sql,
before you start your query do a "begin tran" and when you are comfortable with it then "commit tran",
and in case the results aren't pretty you do a "rollback tran"

Offline AnonymousUser

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Feb 2013
  • Posts: 2924
  • Total likes: 1
  • DansDeals.com Hat Tips 0
    • View Profile
Re: SQL Query help
« Reply #10 on: December 26, 2014, 10:21:59 AM »
transaction is fairly simple, it is good to be used when making any changes in sql,
before you start your query do a "begin tran" and when you are comfortable with it then "commit tran",
and in case the results aren't pretty you do a "rollback tran"
Cool, thanks.

Offline Alexsei

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2011
  • Posts: 1953
  • Total likes: 14
  • DansDeals.com Hat Tips 1
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Champlain NY, with 1' in canada
Re: SQL Query help
« Reply #11 on: December 26, 2014, 10:25:19 AM »
Thanks everyone! this community is amazing !
Слава Україні! Героям слава!

Offline lubaby

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jun 2010
  • Posts: 3243
  • Total likes: 49
  • DansDeals.com Hat Tips 12
  • Gender: Male
    • View Profile
  • Location: New York
  • Programs: Hertz PC, National EE
Re: SQL Query help
« Reply #12 on: December 26, 2014, 10:55:31 AM »
Cool, thanks.
+1
You learn new things every day  :)
Once the game is over, the king and the pawn go back in the same box.

Offline smurf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Sep 2010
  • Posts: 4388
  • Total likes: 0
  • DansDeals.com Hat Tips 8
    • View Profile
  • Location: NJ
Re: SQL Query help
« Reply #13 on: December 26, 2014, 03:34:00 PM »
You can always test out SQL like this by doing SELECT instead of DELETE, and reviewing the records to make sure you're deleting the right ones.
That's usually what I do when updating
transaction is fairly simple, it is good to be used when making any changes in sql,
before you start your query do a "begin tran" and when you are comfortable with it then "commit tran",
and in case the results aren't pretty you do a "rollback tran"

Thanks.Going to have to check this out

Offline whYME

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 2208
  • Total likes: 20
  • DansDeals.com Hat Tips 3
  • Gender: Male
    • View Profile
Re: SQL Query help
« Reply #14 on: December 28, 2014, 11:33:33 PM »
You can always test out SQL like this by doing SELECT instead of DELETE, and reviewing the records to make sure you're deleting the right ones.
+1
I always run a SELECT before a DELETE or UPDATE to make sure I got it right.