Author Topic: SQL Query help  (Read 3605 times)

Offline Alexsei

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2011
  • Posts: 2395
  • Total likes: 50
  • DansDeals.com Hat Tips 2
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Champlain NY, with 1' in canada
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?
אם אתם בונים בית חבד בכל מקום אני מראה לכם בית חבד של ציון

Offline yesitsme

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Dec 2014
  • Posts: 806
  • Total likes: 21
  • DansDeals.com Hat Tips 4
    • View Profile
    • perpetual talk
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
be conservative in what you do, be liberal in what you accept from others.

Offline lubaby

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jun 2010
  • Posts: 3617
  • Total likes: 85
  • DansDeals.com Hat Tips 12
  • Gender: Male
    • View Profile
  • Location: New York
  • Programs: Hertz PC, National EE
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.
Once the game is over, the king and the pawn go back in the same box.

Offline yesitsme

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Dec 2014
  • Posts: 806
  • Total likes: 21
  • DansDeals.com Hat Tips 4
    • View Profile
    • perpetual talk
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
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: 2395
  • Total likes: 50
  • DansDeals.com Hat Tips 2
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Champlain NY, with 1' in canada
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  ;)
אם אתם בונים בית חבד בכל מקום אני מראה לכם בית חבד של ציון

Offline yesitsme

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Dec 2014
  • Posts: 806
  • Total likes: 21
  • DansDeals.com Hat Tips 4
    • View Profile
    • perpetual talk
Re: SQL Query help
« Reply #35 on: May 14, 2015, 02:10:13 PM »
NP,
The images you posted are bogus info i hope.
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: 2395
  • Total likes: 50
  • DansDeals.com Hat Tips 2
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Champlain NY, with 1' in canada
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
אם אתם בונים בית חבד בכל מקום אני מראה לכם בית חבד של ציון

Offline Alexsei

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2011
  • Posts: 2395
  • Total likes: 50
  • DansDeals.com Hat Tips 2
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Champlain NY, with 1' in canada
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?
אם אתם בונים בית חבד בכל מקום אני מראה לכם בית חבד של ציון

Offline Alexsei

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2011
  • Posts: 2395
  • Total likes: 50
  • DansDeals.com Hat Tips 2
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Champlain NY, with 1' in canada
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!
אם אתם בונים בית חבד בכל מקום אני מראה לכם בית חבד של ציון

Offline yesitsme

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Dec 2014
  • Posts: 806
  • Total likes: 21
  • DansDeals.com Hat Tips 4
    • View Profile
    • perpetual talk
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
be conservative in what you do, be liberal in what you accept from others.

Offline AsherO

  • Global Moderator
  • Dansdeals Lifetime 20K Presidential Platinum Elite
  • **********
  • Join Date: May 2008
  • Posts: 20524
  • Total likes: 110
  • DansDeals.com Hat Tips 73
    • View Profile
Re: SQL Query help
« Reply #40 on: May 14, 2015, 03:03:33 PM »
In T-SQL (MS SQL Server) you can just say isnull([ColumnName], 'replacenullwiththistext')
PGF24BMGS

Offline Alexsei

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2011
  • Posts: 2395
  • Total likes: 50
  • DansDeals.com Hat Tips 2
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Champlain NY, with 1' in canada
Re: SQL Query help
« Reply #41 on: May 15, 2015, 10:49:59 AM »
אם אתם בונים בית חבד בכל מקום אני מראה לכם בית חבד של ציון

Offline Alexsei

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2011
  • Posts: 2395
  • Total likes: 50
  • DansDeals.com Hat Tips 2
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Champlain NY, with 1' in canada
Re: SQL Query help
« Reply #42 on: July 02, 2015, 12:53:38 PM »
How can we do an exact match search and replace with PHPMyadmin?

I did
Code: [Select]
SET `payment_amount` = replace(payment_amount, '10', '10.00')But it will replace records like 100, 1000 etc.
אם אתם בונים בית חבד בכל מקום אני מראה לכם בית חבד של ציון

Offline JoeyShmoe

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Nov 2014
  • Posts: 993
  • Total likes: 45
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
  • Location: Lakewood
Re: SQL Query help
« Reply #43 on: July 02, 2015, 01:15:59 PM »
How can we do an exact match search and replace with PHPMyadmin?

I did
Code: [Select]
SET `payment_amount` = replace(payment_amount, '10', '10.00')But it will replace records like 100, 1000 etc.

Try
Code: [Select]
SET payment_amount = IF(payment_amount = '10', '10.00', payment_amount)
OR you can do
Code: [Select]
SET `payment_amount` = replace(payment_amount, '10', '10.00') WHERE payment_amount = '10'
Both should work
DDF A-Z Link Extension
Chrome
Firefox
Info

Offline AsherO

  • Global Moderator
  • Dansdeals Lifetime 20K Presidential Platinum Elite
  • **********
  • Join Date: May 2008
  • Posts: 20524
  • Total likes: 110
  • DansDeals.com Hat Tips 73
    • View Profile
Re: SQL Query help
« Reply #44 on: July 02, 2015, 01:21:58 PM »
OR you can do
Code: [Select]
SET `payment_amount` = replace(payment_amount, '10', '10.00') WHERE payment_amount = '10'

This one should be much more efficient.
PGF24BMGS