[SOLVED]How to compare values from one table to another?
Moderator: General Moderators
[SOLVED]How to compare values from one table to another?
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
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.
Re: How to compare values from one table to another?
Post your tables structure.
There are 10 types of people in this world, those who understand binary and those who don't
Re: How to compare values from one table to another?
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
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?
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
Re: How to compare values from one table to another?
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
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?
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
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
Re: How to compare values from one table to another?
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()?
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?
Combined into a single query:
INSERT INTO...SELECT ...ON DUPLICATE KEY UPDATE
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
Re: How to compare values from one table to another?
Hi.
Im still cant tranfer from table to table
Im using this query
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?
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.
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
Re: How to compare values from one table to another?
Its still not working 
Im getting this error
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?
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
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?
1. Remove all aliases (yeah - "table2 a" is an alias).
2. Reread my previous post.
3.
4.
2. Reread my previous post.
3.
use fully qualified names: table1.itemid, tbale2.itemid, etc.If I remove s I get an error itemid is ambigous.
4.
Nope. That's why you specify the source/target columnsBTW for this query to work, should it be that both table have equal amount of columns?
There are 10 types of people in this world, those who understand binary and those who don't
Re: How to compare values from one table to another?
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.
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+tableqty1Re: How to compare values from one table to another?
Code: Select all
table2.qty=table2.qty+tableqty1read your previous intent and you should figure it out
Re: How to compare values from one table to another?
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
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