Member Avatar for 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 :)

  • 4 Contributors
  • 9 Replies
  • 157 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by trishtren
Member Avatar for pritaeas

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

Member Avatar for 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
Member Avatar for 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.

Member Avatar for pritaeas

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

INSERT INTO TableB (ItemName, ID)
SELECT ItemName, ID FROM TableA
Member Avatar for 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
Edited by adam_k
Member Avatar for 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

Member Avatar for 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.

Member Avatar for 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
Member Avatar for 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...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.