[SOLVED]How to compare values from one table to another?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Bbob
Forum Commoner
Posts: 57
Joined: Sat Aug 07, 2010 4:46 am

[SOLVED]How to compare values from one table to another?

Post by Bbob »

Hi

How do I compare values from one table to another? I trying to check if an item already exist in a table.

Ex. letter = value
table 1 has q=10, w=20, e=30, r=40, t=50, y=60
table 2 has a=11, s=22, d=33, f=44, r=55, t=66, y=77

How do I check if the letter from table 1 exist in table 2?

And If the letter from table 1 exist in table 2, how do I update the values of table 2?

From ex.
Since t both exist in table and table 2, I need to update its value - in the end the value of t from table 2 should be 116
Last edited by Bbob on Fri Nov 26, 2010 7:06 am, edited 3 times in total.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: How to compare values from one table to another?

Post by VladSun »

Post your tables structure.
There are 10 types of people in this world, those who understand binary and those who don't
Bbob
Forum Commoner
Posts: 57
Joined: Sat Aug 07, 2010 4:46 am

Re: How to compare values from one table to another?

Post by Bbob »

Hi

What do you mean by table structure? Sorry, Im not familiar with terms :oops:

If you mean, the table fields...
(These are my original table fields)

Table 1
pid | itemid | name | qty | price

Table 2
itemid | name | qty | price
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: How to compare values from one table to another?

Post by VladSun »

So ... having these tables structure, what's the meaning of "if the letter from table 1 exist in table 2"?
There are 10 types of people in this world, those who understand binary and those who don't
Bbob
Forum Commoner
Posts: 57
Joined: Sat Aug 07, 2010 4:46 am

Re: How to compare values from one table to another?

Post by Bbob »

Table 1
pid | itemid | name | qty | price
1 | 1 | pen | 100 | 5
1 | 2 | paper | 90 | 10
1 | 3 | rock | 80 | 15
2 | 4 | scissor | 20 | 10

Table 2
itemid | name | qty | price
1 | pen | 20 | 5
2 | paper | 10 | 10

I want to transfer all items with pid = 1 from table 1 to table 2.

a) Item Rock does not exist in Table 2 so transferring to Table 2 is simple.

b) Item pen and paper, on the other hand already exist in Table 2, so I wont need to transfer the whole row but will only need to add Table 1 pen and paper qty to Table 2 pen and paper qty.


In the end the content of Table 2 should be

Table 2
itemid | name | qty | price
1 | pen | 120 | 5
2 | paper | 100 | 10
3 | rock | 80 | 15

=====

What Im having a hard time doing is letter b
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: How to compare values from one table to another?

Post by VladSun »

You need a single query to do all of this (updating existing rows and inserting new rows).
Take a look at INSERT INTO ... SELECT syntax: http://dev.mysql.com/doc/refman/5.0/en/ ... elect.html
and INSERT ... ON DUPLICATE KEY UPDATE Syntax : http://dev.mysql.com/doc/refman/5.0/en/ ... icate.html

Table2.itemid should be a primary key in order to have this working
There are 10 types of people in this world, those who understand binary and those who don't
Bbob
Forum Commoner
Posts: 57
Joined: Sat Aug 07, 2010 4:46 am

Re: How to compare values from one table to another?

Post by Bbob »

Hi

You said I have to do this in a single query, do you mean I have use INSERT INTO...SELECT and INSERT...ON DUPLICATE KEY UPDATE in a single mysql_query()?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: How to compare values from one table to another?

Post by VladSun »

Combined into a single query:
INSERT INTO...SELECT ...ON DUPLICATE KEY UPDATE
There are 10 types of people in this world, those who understand binary and those who don't
Bbob
Forum Commoner
Posts: 57
Joined: Sat Aug 07, 2010 4:46 am

Re: How to compare values from one table to another?

Post by Bbob »

Hi.

Im still cant tranfer from table to table

Im using this query

Code: Select all

INSERT INTO table2 a (itemid, itemname, price, qty)
SELECT itemid, itemname, qty, price
FROM table1 s
WHERE pid = 1
ON DUPLICATE KEY a.qty=a.qty+s.qty 
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: How to compare values from one table to another?

Post by VladSun »

You're close :)
Check your ON DUPLICATE KEY UPDATE syntax ;)

And ... don't use table alias names when not needed... as in your query...
Also, make sure table2.itemid column is a primary key.
There are 10 types of people in this world, those who understand binary and those who don't
Bbob
Forum Commoner
Posts: 57
Joined: Sat Aug 07, 2010 4:46 am

Re: How to compare values from one table to another?

Post by Bbob »

Its still not working :(

Im getting this error

Code: Select all

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a (itemid, itemname, price, qty) SELECT it' at line 1
I also made sure that table2.id is a primary key


What do you mean by table alias? Is that s in table1 s?
If I remove s I get an error itemid is ambigous.

======================

BTW for this query to work, should it be that both table have equal amount of columns?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: How to compare values from one table to another?

Post by VladSun »

1. Remove all aliases (yeah - "table2 a" is an alias).
2. Reread my previous post.
3.
If I remove s I get an error itemid is ambigous.
use fully qualified names: table1.itemid, tbale2.itemid, etc.

4.
BTW for this query to work, should it be that both table have equal amount of columns?
Nope. That's why you specify the source/target columns ;)
There are 10 types of people in this world, those who understand binary and those who don't
Bbob
Forum Commoner
Posts: 57
Joined: Sat Aug 07, 2010 4:46 am

Re: How to compare values from one table to another?

Post by Bbob »

I can insert the values now yey :D

But I get a value of 0 for qty :(

Please help, its so close to finishing it ><

heres my query for qty party.

Code: Select all

table2.qty=table2.qty+tableqty1
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: How to compare values from one table to another?

Post by mikosiko »

Code: Select all

table2.qty=table2.qty+tableqty1
do you have in some place something called tableqty1 ?

read your previous intent and you should figure it out
Bbob
Forum Commoner
Posts: 57
Joined: Sat Aug 07, 2010 4:46 am

Re: How to compare values from one table to another?

Post by Bbob »

Its working now :D

Thanks to everyone that help me, especially VladSun :D :D :D



============

Ohh...last question. Is it possible to add AND in ON DUPLICATE KEY UPDATE? If so can you show me how.

Im using it like this but it doesnt work, it even turns qty to 0

Code: Select all

INSERT INTO table2 (itemid, itemname, price, qty)
SELECT itemid, itemname, qty, price
FROM table1
WHERE pid = 1
ON DUPLICATE KEY UPDATE table2.qty=table2.qty+table1.qty 
AND table2.price=table2.price+table1.price
Post Reply