Author Topic: SQL Query help  (Read 3017 times)

Offline Alexsei

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2011
  • Posts: 1951
  • 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 #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: 787
  • Total likes: 13
  • 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: 3242
  • 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 #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: 787
  • Total likes: 13
  • 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: 1951
  • 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 #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: 787
  • Total likes: 13
  • 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: 1951
  • 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 #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: 1951
  • 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 #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: 1951
  • 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 #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: 787
  • Total likes: 13
  • 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: 20376
  • Total likes: 75
  • 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')
DDF's version of Godwin's Law: As a DDF thread grows longer, the probability of it pivoting to religion approaches 1...

Offline Alexsei

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2011
  • Posts: 1951
  • 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 #41 on: May 15, 2015, 10:49:59 AM »
Слава Україні! Героям слава!

Offline Alexsei

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2011
  • Posts: 1951
  • 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 #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: 858
  • Total likes: 18
  • 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

Offline AsherO

  • Global Moderator
  • Dansdeals Lifetime 20K Presidential Platinum Elite
  • **********
  • Join Date: May 2008
  • Posts: 20376
  • Total likes: 75
  • 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.
DDF's version of Godwin's Law: As a DDF thread grows longer, the probability of it pivoting to religion approaches 1...