Page 1 of 1
[SOLVED]How to compare values from one table to another?
Posted: Tue Nov 16, 2010 9:46 am
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
Re: How to compare values from one table to another?
Posted: Wed Nov 17, 2010 2:06 am
by VladSun
Post your tables structure.
Re: How to compare values from one table to another?
Posted: Wed Nov 17, 2010 3:18 am
by Bbob
Hi
What do you mean by table structure? Sorry, Im not familiar with terms
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
Re: How to compare values from one table to another?
Posted: Wed Nov 17, 2010 3:34 am
by VladSun
So ... having these tables structure, what's the meaning of "if the letter from table 1 exist in table 2"?
Re: How to compare values from one table to another?
Posted: Thu Nov 18, 2010 12:33 am
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
Re: How to compare values from one table to another?
Posted: Thu Nov 18, 2010 3:08 am
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
Re: How to compare values from one table to another?
Posted: Thu Nov 18, 2010 6:11 am
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()?
Re: How to compare values from one table to another?
Posted: Thu Nov 18, 2010 6:12 am
by VladSun
Combined into a single query:
INSERT INTO...SELECT ...ON DUPLICATE KEY UPDATE
Re: How to compare values from one table to another?
Posted: Fri Nov 19, 2010 6:15 am
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
Re: How to compare values from one table to another?
Posted: Fri Nov 19, 2010 7:22 am
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.
Re: How to compare values from one table to another?
Posted: Fri Nov 19, 2010 10:21 am
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?
Re: How to compare values from one table to another?
Posted: Fri Nov 19, 2010 10:58 am
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

Re: How to compare values from one table to another?
Posted: Fri Nov 19, 2010 11:14 am
by Bbob
I can insert the values now yey
But I get a value of 0 for qty
Please help, its so close to finishing it ><
heres my query for qty party.
Re: How to compare values from one table to another?
Posted: Fri Nov 19, 2010 11:58 am
by mikosiko
do you have in some place something called
tableqty1 ?
read your previous intent and you should figure it out
Re: How to compare values from one table to another?
Posted: Fri Nov 19, 2010 11:21 pm
by Bbob
Its working now
Thanks to everyone that help me, especially VladSun
============
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