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 :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

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 :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

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

Posted: Fri Nov 19, 2010 11:58 am
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

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

Posted: Fri Nov 19, 2010 11:21 pm
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