Author Topic: SQL Query help  (Read 3115 times)

Offline Alexsei

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2011
  • Posts: 2000
  • Total likes: 18
  • DansDeals.com Hat Tips 2
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Champlain NY, with 1' in canada
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)
Слава Україні! Героям слава!

Offline AnonymousUser

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Feb 2013
  • Posts: 2937
  • Total likes: 2
  • 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.

Online etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 8302
  • Total likes: 106
  • 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 Platinum Elite
  • *******
  • Join Date: Sep 2011
  • Posts: 2000
  • Total likes: 18
  • DansDeals.com Hat Tips 2
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Champlain NY, with 1' in canada
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 »
Слава Україні! Героям слава!

Offline an613

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Apr 2010
  • Posts: 320
  • Total likes: 0
  • 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)

Offline yesitsme

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Dec 2014
  • Posts: 787
  • Total likes: 13
  • DansDeals.com Hat Tips 4
    • View Profile
    • perpetual talk
Re: SQL Query help
« Reply #20 on: April 30, 2015, 10:37:54 PM »
I imported a document ods utf-8 to mySql CHARACTER SET utf8 COLLATE utf8_unicode_ci it has a few Hebrew columns, in the database it's readable when I fetch it using php <meta charset="utf-8"> i get ?????????   ????????????, anyone had this? any solution?

Edit:
 my ? turned into smilies on smf not in php  [? ? ?] => ???

  [smiley_replacements] => Array
        (
            [> : D] => >:D
            [> : D] => >:D
            [: D] => :D
            [:: )] => ::)
            [> : (] => >:(
            [> : (] => >:(
            [: ))] => :))
            [: )] => :)
            [; )] => ;)
            [; D] => ;D
            [: (] => :(
            [: o] => :o
            [8 )] => 8)
            [: P] => :P
            [?? ?] => ???
            [: -[] => :-[
            [: -X] => :-X
            [: -*] => :-*
            [:' (] => :'(
            [: '(] => :'(
            [: -\] => :-\
            [^ -^] => ^-^
            [O 0] => O0
            [C :-)] => C:-)
            [0 : )] => 0:)
        )
« Last Edit: May 01, 2015, 01:17:12 AM by yesitsme »
be conservative in what you do, be liberal in what you accept from others.

Offline yesitsme

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Dec 2014
  • Posts: 787
  • Total likes: 13
  • DansDeals.com Hat Tips 4
    • View Profile
    • perpetual talk
Re: SQL Query help
« Reply #21 on: May 01, 2015, 01:39:28 AM »
solved in php   
Code: [Select]
$dsn = "mysql:host=$host;dbname=$dbname;charset=utf8;"
I added
Code: [Select]
charset=utf8;
I guess php doesn't speak Hebrew
be conservative in what you do, be liberal in what you accept from others.

Offline Alexsei

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2011
  • Posts: 2000
  • Total likes: 18
  • DansDeals.com Hat Tips 2
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Champlain NY, with 1' in canada
Re: SQL Query help
« Reply #22 on: May 03, 2015, 10:33:12 PM »
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)

http://www.w3schools.com/sql/sql_join.asp ?
Слава Україні! Героям слава!

Offline yesitsme

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Dec 2014
  • Posts: 787
  • Total likes: 13
  • DansDeals.com Hat Tips 4
    • View Profile
    • perpetual talk
Re: SQL Query help
« Reply #23 on: May 03, 2015, 11:08:43 PM »
be conservative in what you do, be liberal in what you accept from others.

Offline Alexsei

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2011
  • Posts: 2000
  • Total likes: 18
  • DansDeals.com Hat Tips 2
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Champlain NY, with 1' in canada
Re: SQL Query help
« Reply #24 on: May 13, 2015, 07:00:00 PM »
what don't you understand?
Can't figure, is someone ready to help with exact instructions? Please  ;)
Слава Україні! Героям слава!

Offline yesitsme

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Dec 2014
  • Posts: 787
  • Total likes: 13
  • DansDeals.com Hat Tips 4
    • View Profile
    • perpetual talk
Re: SQL Query help
« Reply #25 on: May 14, 2015, 01:09:41 AM »
Can't figure, is someone ready to help with exact instructions? Please  ;)
#1 Import the excel sheet to a new table in the DB
#2 Merge

If I'm not mistaken join returns a result (output array), merge merges 2 tables.
be conservative in what you do, be liberal in what you accept from others.

Offline Alexsei

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2011
  • Posts: 2000
  • Total likes: 18
  • DansDeals.com Hat Tips 2
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Champlain NY, with 1' in canada
Re: SQL Query help
« Reply #26 on: May 14, 2015, 11:50:37 AM »
#1 Import the excel sheet to a new table in the DB
#2 Merge

If I'm not mistaken join returns a result (output array), merge merges 2 tables.
The excel is already in its own table however i have no idea how to write the query
Слава Україні! Героям слава!

Offline yesitsme

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Dec 2014
  • Posts: 787
  • Total likes: 13
  • DansDeals.com Hat Tips 4
    • View Profile
    • perpetual talk
Re: SQL Query help
« Reply #27 on: May 14, 2015, 12:19:32 PM »
Code: [Select]
update tsql c
join texcel u on u.customer_firstname = c.customer_firstname
and u.customer_lastname = c.customer_lastname
set c.customer_id = u.customer_id

change
Code: [Select]
texcel to your excel table,
&
 
Code: [Select]
tsql to your sql table

are you using ms or my?
be conservative in what you do, be liberal in what you accept from others.

Offline Alexsei

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2011
  • Posts: 2000
  • Total likes: 18
  • DansDeals.com Hat Tips 2
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Champlain NY, with 1' in canada
Re: SQL Query help
« Reply #28 on: May 14, 2015, 12:31:00 PM »
Code: [Select]
update tsql c
join texcel u on u.customer_firstname = c.customer_firstname
and u.customer_lastname = c.customer_lastname
set c.customer_id = u.customer_id

change
Code: [Select]
texcel to your excel table,
&
 
Code: [Select]
tsql to your sql table

are you using ms or my?
Thanks, got this error:
Code: [Select]
#1054 - Unknown column 'u.customer_firstname' in 'on clause'
Слава Україні! Героям слава!

Offline yesitsme

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Dec 2014
  • Posts: 787
  • Total likes: 13
  • DansDeals.com Hat Tips 4
    • View Profile
    • perpetual talk
Re: SQL Query help
« Reply #29 on: May 14, 2015, 12:58:44 PM »
are you using ms Sql or mySql?

Did did you rename the tables?
Do the columns match exact names?
be conservative in what you do, be liberal in what you accept from others.