Author Topic: SQL Query help  (Read 10883 times)

Offline yesitsme

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2014
  • Posts: 5020
  • Total likes: 2237
  • DansDeals.com Hat Tips 4
  • Gender: Male
    • View Profile
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 »
["-"]

Offline yesitsme

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2014
  • Posts: 5020
  • Total likes: 2237
  • DansDeals.com Hat Tips 4
  • Gender: Male
    • View Profile
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
["-"]

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 #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 ?
Jews ≠ Zionists
Palestinians ≠ Hamas
Satmar ≠ SatmarHQ

Offline yesitsme

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2014
  • Posts: 5020
  • Total likes: 2237
  • DansDeals.com Hat Tips 4
  • Gender: Male
    • View Profile
Re: SQL Query help
« Reply #23 on: May 03, 2015, 11:08:43 PM »
["-"]

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 #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  ;)
Jews ≠ Zionists
Palestinians ≠ Hamas
Satmar ≠ SatmarHQ

Offline yesitsme

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2014
  • Posts: 5020
  • Total likes: 2237
  • DansDeals.com Hat Tips 4
  • Gender: Male
    • View Profile
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.
["-"]

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 #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
Jews ≠ Zionists
Palestinians ≠ Hamas
Satmar ≠ SatmarHQ

Offline yesitsme

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2014
  • Posts: 5020
  • Total likes: 2237
  • DansDeals.com Hat Tips 4
  • Gender: Male
    • View Profile
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?
["-"]

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 #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'
Jews ≠ Zionists
Palestinians ≠ Hamas
Satmar ≠ SatmarHQ

Offline yesitsme

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2014
  • Posts: 5020
  • Total likes: 2237
  • DansDeals.com Hat Tips 4
  • Gender: Male
    • View Profile
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?
["-"]

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 #30 on: May 14, 2015, 01:35:03 PM »
Did did you rename the tables?
Do the columns match exact names?

mySql

Column's match exactly.
Rename table, do you mean in the query?
Jews ≠ Zionists
Palestinians ≠ Hamas
Satmar ≠ SatmarHQ

Offline yesitsme

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2014
  • Posts: 5020
  • Total likes: 2237
  • DansDeals.com Hat Tips 4
  • Gender: Male
    • View Profile
Re: SQL Query help
« Reply #31 on: May 14, 2015, 01:45:38 PM »
Rename table, do you mean in the query?

yes, what did you think? (read with a smile i'm asking not screaming)

can you post your query?

since i don't know your table name i can't put it in the query unless you tell me
["-"]

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 #32 on: May 14, 2015, 01:52:32 PM »
As a beginner to SQL, please run all your queries as SELECT before you do any UPDATE. Make sure everything looks the way it's meant to with the SELECT before you UPDATE.
You cannot undo an UPDATE (unless you have DB backups which you can restore).

Or learn how to do TRANSACTIONS, where you can ROLLBACK before you COMMIT.

Offline yesitsme

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2014
  • Posts: 5020
  • Total likes: 2237
  • DansDeals.com Hat Tips 4
  • Gender: Male
    • View Profile
Re: SQL Query help
« Reply #33 on: May 14, 2015, 01:58:30 PM »
i just noticed the you have a S cutomer's'_... not in the query
["-"]

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 #34 on: May 14, 2015, 02:04:00 PM »
i just noticed the you have a S cutomer's'_... not in the query
That's it worked now! Thanks so much, lookin fwd for your help next time  ;)
Jews ≠ Zionists
Palestinians ≠ Hamas
Satmar ≠ SatmarHQ

Offline yesitsme

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2014
  • Posts: 5020
  • Total likes: 2237
  • DansDeals.com Hat Tips 4
  • Gender: Male
    • View Profile
Re: SQL Query help
« Reply #35 on: May 14, 2015, 02:10:13 PM »
NP,
The images you posted are bogus info i hope.
["-"]

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 #36 on: May 14, 2015, 02:13:05 PM »
NP,
The images you posted are bogus info i hope.
fakenamegenerator.com came in handy
Jews ≠ Zionists
Palestinians ≠ Hamas
Satmar ≠ SatmarHQ

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 #37 on: May 14, 2015, 02:33:13 PM »
As a beginner to SQL, please run all your queries as SELECT before you do any UPDATE. Make sure everything looks the way it's meant to with the SELECT before you UPDATE.
You cannot undo an UPDATE (unless you have DB backups which you can restore).

Or learn how to do TRANSACTIONS, where you can ROLLBACK before you COMMIT.

How would i do it in the below case:
 
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?
Jews ≠ Zionists
Palestinians ≠ Hamas
Satmar ≠ SatmarHQ

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 #38 on: May 14, 2015, 02:46: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

How do i add a IFNULL statement? it should only update if the original value is null
Thanks!
Jews ≠ Zionists
Palestinians ≠ Hamas
Satmar ≠ SatmarHQ

Offline yesitsme

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2014
  • Posts: 5020
  • Total likes: 2237
  • DansDeals.com Hat Tips 4
  • Gender: Male
    • View Profile
Re: SQL Query help
« Reply #39 on: May 14, 2015, 02:59:51 PM »
update tsql c
join texcel u on u.customers_firstname = c.customers_firstname
and u.customers_lastname = c.customers_lastname
and c.customers_id is null
set c.customers_id = u.customers_id
["-"]