LinuxQuestions.org
Latest LQ Deal: Linux Power User Bundle
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 06-09-2015, 01:34 AM   #1
sam@
Member
 
Registered: Sep 2013
Posts: 31

Rep: Reputation: Disabled
Question Compare and Update 2 columns from 2 tables in mysql


So I used the query to find the entries:

Code:
select distinct m.name, item_count, item from master m join client p on m.name=p.name where item_count = 1 and item > 1;
if i use this update

Code:
Update from client Set item =1  where m.name=p.name and item_count=1 AND item>1
Is this wrong?

Last edited by sam@; 06-19-2015 at 02:16 AM.
 
Old 06-09-2015, 01:51 AM   #2
sam@
Member
 
Registered: Sep 2013
Posts: 31

Original Poster
Rep: Reputation: Disabled
mistake

Last edited by sam@; 06-19-2015 at 02:17 AM.
 
Old 06-09-2015, 09:27 AM   #3
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,491

Rep: Reputation: 2867Reputation: 2867Reputation: 2867Reputation: 2867Reputation: 2867Reputation: 2867Reputation: 2867Reputation: 2867Reputation: 2867Reputation: 2867Reputation: 2867
What makes a row unique in your examples? Or are you happy to update all that meet the criteria irrelevant of uniqueness?

I am not sure I follow the second example
 
Old 06-10-2015, 10:40 PM   #4
sam@
Member
 
Registered: Sep 2013
Posts: 31

Original Poster
Rep: Reputation: Disabled
The name is unique identifier.

I select all entries wherein i join the tables using the names and list out all entries whose item _count=1 but item >1

I use this query to do so:
Code:
select distinct m.name, item_count, item from master m join client p on m.name=p.name where item_count = 1 and item > 1;
This query gives me list of entries ie. name item_count and item
I need to update all these entries to item =1

pLEASE ignore the second comment
 
Old 06-11-2015, 01:07 AM   #5
davedpss
LQ Newbie
 
Registered: May 2011
Posts: 15

Rep: Reputation: 0
Untested.

Update client p
INNER JOIN master m ON p.name = m.name
SET item = 1
WHERE m.item_count = 1
AND p.item > 1
 
Old 06-11-2015, 02:58 AM   #6
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,491

Rep: Reputation: 2867Reputation: 2867Reputation: 2867Reputation: 2867Reputation: 2867Reputation: 2867Reputation: 2867Reputation: 2867Reputation: 2867Reputation: 2867Reputation: 2867
Well I am not sure how you think the name is unique in either table when both have 'Straight6' more than once in the name column??

The updates based on your select, as shown above, will update more than uniquely identified columns. This is not bad, but make sure it is what you want.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Need to compare 2 tables data from different linux servers with mysql using perl script tarun28jain Programming 3 10-16-2012 06:54 AM
MySQL: Trying to Drop Columns Named update and delete weibullguy Programming 4 06-13-2008 12:54 PM
MySQL - Query and compare 2 tables rookiepaul Programming 4 05-12-2008 09:05 AM
compare $php variable to indexed distinct mysql columns secretlydead Programming 1 02-18-2008 10:48 PM
Mysql Compare two tables from different databases baddah Programming 4 04-20-2006 12:36 AM


All times are GMT -5. The time now is 11:51 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration