?
Solved

MYSQL Update Query with INNER JOIN returning 0 rows

Posted on 2011-04-27
6
Medium Priority
?
439 Views
Last Modified: 2012-05-11
Hi Experts -

The following query is updating 0 rows, but if I do a select statement with the same join and where i get 23 records. Can you tell me what i am typing wrong?

¿¿¿¿¿¿update Price inner join popular on popular.SKU = Price.SKU set Price.Featured = 3 where Price.Featured <> 2;

Thanks for the help.
0
Comment
Question by:danjen
  • 4
  • 2
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35480151
can you show the SELECT anyhow?
except that you change "something" essential in the query, the update should be ok...
 
0
 

Author Comment

by:danjen
ID: 35480211
Well that helped narrow it down a little bit:

select *
FROM
Price
INNER JOIN popular ON popular.SKU = Price.SKU

This query returns 23 rows

If I do this I get 0 rows;

select *
FROM
Price
INNER JOIN popular ON popular.SKU = Price.SKU where Price.Featured <> 2

There are 5000 records in the Price file and only 15 have the value 2 in the Featured column. So the other 4000 should be listed.

Thanks for the help.

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35480245
>So the other 4000 should be listed.
only if they have a matching record in the popular table (based on the SKU value) !
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35480248
and your first query, returning "only" 23 rows, shows that most records in price do NOT have the SKU in popular table.
0
 

Author Comment

by:danjen
ID: 35480251
i figured out a solution but maybe you can explain:

when I leave off the where I noticed that column Featured contained all NULL records which are not equal to 2 so they should have been listed. If I change the where to "Where Featured is null" the update worked.

Any ideas
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 35480278
what about removing the join altogether?
as to the "why" : it's because you have no matching records in the popular table... or NULL in the Featured  column ...

update Price inner join popular on popular.SKU = Price.SKU set Price.Featured = 3 where Price.Featured <> 2 or Price.Features IS NULL; 

Open in new window


or

update Price set Featured = 3 where Featured <> 2 or Price.Features IS NULL; 

Open in new window

0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month16 days, 18 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question