Author Topic: SQL Query help  (Read 10875 times)

Offline Alexsei

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Sep 2011
  • Posts: 5789
  • Total likes: 1421
  • DansDeals.com Hat Tips 5
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Truckistan
  • Programs: COVID-23
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
Jews ≠ Zionists
Palestinians ≠ Hamas
Satmar ≠ SatmarHQ

Offline AnonymousUser

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Feb 2013
  • Posts: 3001
  • Total likes: 13
  • 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 Presidential Platinum Elite
  • *********
  • Join Date: Sep 2011
  • Posts: 5789
  • Total likes: 1421
  • DansDeals.com Hat Tips 5
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Truckistan
  • Programs: COVID-23
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);
Jews ≠ Zionists
Palestinians ≠ Hamas
Satmar ≠ SatmarHQ

Offline AnonymousUser

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Feb 2013
  • Posts: 3001
  • Total likes: 13
  • 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 Presidential Platinum Elite
  • *********
  • Join Date: Sep 2011
  • Posts: 5789
  • Total likes: 1421
  • DansDeals.com Hat Tips 5
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Truckistan
  • Programs: COVID-23
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 »
Jews ≠ Zionists
Palestinians ≠ Hamas
Satmar ≠ SatmarHQ

Offline AnonymousUser

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Feb 2013
  • Posts: 3001
  • Total likes: 13
  • 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: 86
  • 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 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • 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: 3001
  • Total likes: 13
  • 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: 86
  • 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: 3001
  • Total likes: 13
  • 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 Presidential Platinum Elite
  • *********
  • Join Date: Sep 2011
  • Posts: 5789
  • Total likes: 1421
  • DansDeals.com Hat Tips 5
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Truckistan
  • Programs: COVID-23
Re: SQL Query help
« Reply #11 on: December 26, 2014, 10:25:19 AM »
Thanks everyone! this community is amazing !
Jews ≠ Zionists
Palestinians ≠ Hamas
Satmar ≠ SatmarHQ

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5335
  • Total likes: 736
  • DansDeals.com Hat Tips 12
    • View Profile
Re: SQL Query help
« Reply #12 on: December 26, 2014, 10:55:31 AM »
Cool, thanks.
+1
You learn new things every day  :)

Offline smurf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Sep 2010
  • Posts: 4525
  • Total likes: 276
  • 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 Presidential Platinum Elite
  • ********
  • Join Date: May 2008
  • Posts: 3213
  • Total likes: 1241
  • 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.

Offline Alexsei

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Sep 2011
  • Posts: 5789
  • Total likes: 1421
  • DansDeals.com Hat Tips 5
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Truckistan
  • Programs: COVID-23
Re: SQL Query help
« Reply #15 on: April 30, 2015, 06:14:18 PM »
I have a spreadsheet with 3 columns
ID, First Name, Last Name.

In SQL i have a table that has the above 3 columns and others the ID field is empty, what i'm trying to accomplish is adding the ID from the excel into the sql table, is there a way to match the right row and add it in the correct row? (The excel file does not contain the exact same list as the sql)
Jews ≠ Zionists
Palestinians ≠ Hamas
Satmar ≠ SatmarHQ

Offline AnonymousUser

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Feb 2013
  • Posts: 3001
  • Total likes: 13
  • DansDeals.com Hat Tips 0
    • View Profile
Re: SQL Query help
« Reply #16 on: April 30, 2015, 07:46:59 PM »
I have a spreadsheet with 3 columns
ID, First Name, Last Name.

In SQL i have a table that has the above 3 columns and others the ID field is empty, what i'm trying to accomplish is adding the ID from the excel into the sql table, is there a way to match the right row and add it in the correct row? (The excel file does not contain the exact same list as the sql)
Can you clarify? I didn't really understand what you're trying to do. Although it sounds like JOIN might work.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: SQL Query help
« Reply #17 on: April 30, 2015, 08:35:55 PM »
I would copy the SQL table to excel, then use vlookup to fill in the IDs of the data from SQL, then copy and paste that back into the database, or import it, or something.
Workflowy. You won't know what you're missing until you try it.

Offline Alexsei

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Sep 2011
  • Posts: 5789
  • Total likes: 1421
  • DansDeals.com Hat Tips 5
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Truckistan
  • Programs: COVID-23
Re: SQL Query help
« Reply #18 on: April 30, 2015, 09:13:22 PM »
Can you clarify? I didn't really understand what you're trying to do. Although it sounds like JOIN might work.

I would copy the SQL table to excel, then use vlookup to fill in the IDs of the data from SQL, then copy and paste that back into the database, or import it, or something.

You both seem to have good ideas, but with my little knowledge I would need step by step instructions.....

To explain let me show an example:
Here is how it would look in the SQL



And this is how it looks like in the file i want to import;


So basically
  • The file does not have all columns that the db has only the names and ID
  • There are names that aren't present in the db, there are names from the database that aren't present in the file
  • if both the first and last name is a match than the # in ID field should be recorded
« Last Edit: April 30, 2015, 09:18:37 PM by Alexsei »
Jews ≠ Zionists
Palestinians ≠ Hamas
Satmar ≠ SatmarHQ

Offline an613

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Apr 2010
  • Posts: 319
  • Total likes: 2
  • DansDeals.com Hat Tips 1
    • View Profile
Re: SQL Query help
« Reply #19 on: April 30, 2015, 10:07:02 PM »

You both seem to have good ideas, but with my little knowledge I would need step by step instructions.....

To explain let me show an example:
Here is how it would look in the SQL



And this is how it looks like in the file i want to import;


So basically
  • The file does not have all columns that the db has only the names and ID
  • There are names that aren't present in the db, there are names from the database that aren't present in the file
  • if both the first and last name is a match than the # in ID field should be recorded

What DB is it and are you comfortable coding at all? Easiest option is probably a 10 line python script that will read the excel file and then update the rows in the DB.

Alternatively most DBs have an import function for csv files. Save the excel file as a CSV, import it into a temporary table in the DB and then you can do an update across tables. Depending on the DB, you might even be able to do the update straight from the csv without creating a temp table.

Haven't checked this would work but  something similar to "update myrealtable real set id = (select id from mycsv.csv csv where real.name=csv.name)