Comparing elements of a different table

[trishtren]

Hello, im a beginner at sql and i am struggling at comparing data from one table to another and then copying other data.
*I have 2 tables both of which have 2 columns with the same name lets call them tableA and tableB.
* tableA has a column id and tableb has a columnid
* and i want to insert the item name thats in tableA into the empty column in tableB

INSERT INTO tableb (itemname)
SELECT
  tableA.itemName,itemA.ID as a_role,tableB.ID as b_role
FROM tableA,tableB
WHERE a_role=b_role

But this throws me an error saying : #1054 - Unknown column 'a_role' in 'where clause'
Anyone any idea how to accomplish this seemingly simple task? Iv spent a long time googling solutions and i done think im far away from an answer but any help would be nice :)

[pritaeas]

Can you give some sample data for both tables? It will help visualize.

[MikeSmithDev]

If you are trying to put values into tableB, then you probably want to do an UPDATE statement, like

UPDATE tableB 
SET itemName = a.itemName
FROM tableB B
INNER JOIN tableA A ON B.id = A.id

[trishtren]

Some example data is :
TableA:

**ItemName****ID**
   CD         2
   Curtains   8
   Screen     10
   Desk       5     

TableB:

**ItemName****ID**
   CD         
   Curtains  
   Screen     
   Desk   

I want to compare item names so if tableA.ItemName = tableB.Itemname i copy its id into tableB. Note tableB will be empty up until this point im doing this whilst creating the tables. ANy help would be good. I tried mikes way but to no avail.

[pritaeas]

Oh, it's empty. Then you can just:

INSERT INTO TableB (ItemName, ID)
SELECT ItemName, ID FROM TableA

[adam_k]

Only the ID appears empty to me, so in order to update that use:

update TableB
set ID = a.ID 
from TableA a 
inner join TableB b 
on a.ItemName = b.ItemName

[trishtren]

yes that works but i still dont have the condition
since i need to make sure the item names are the same so it doesent assign one a wrong ID if other data is inserted. thanks though

[trishtren]

i mean, only the ID column is empty, the item name column has data in both, with some being similar so i want only the elements with the same name to copy their ID's if that makes sense.

[MikeSmithDev]

Ok then what I posted before was correct, just change the fields if you want to update the ID from the itemName:

   UPDATE tableB
   SET id = a.id
   FROM tableB B
   INNER JOIN tableA A ON B.itemName = A.itemName

[trishtren]

hmm thanks adam i think it nearly works but i seem to be getting this error:

1064 - 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 'from TableA a

inner join Tableb b
on a.ItemName = b.ItemName' at line 3

dunno why it says its wrong...