INSERT TABLE1 FROM TABLE2 BASED ON TABLE3

[trenttc]

TABLE1 gets a copy of the TABLE2 record when TABLE2's ID field matches the ID field of TABLE3. The TABLE2 ID field is not unique. The TABLE3 ID field is unique.
Here's some pseudo code:
INSERT INTO TABLE1
(ID,CATEGORY,PRICE)
SELECT ID,CATEGORY,PRICE
FROM TABLE2
WHEN TABLE2.ID = TABLE3.ID

Thanks in advance.

 

[grega]

Sure you're almost there!

insert into table1 (id, category, price)
select a.id, a.category, a.price
from table2 a, table3 b
where a.id = b.id

Greg.

 

[trenttc]

To grega
It works. Thanks. I ran the query with aliases on this line
(select a.id, a.category, a.price) and then witout aliases
(select id, category, price). It's probably done for clarity here but when is an alias necessary? What are the rules?

 

[grega]

In this case I would always use an alias, as you're joining 2 tables on a column with the same name, i.e. the id field on table2/table3. Most DBs would throw up an error if you didn't use an alias, as they wouldn't know which table to take the id value from.

I also use aliasing for simplicity, it's much easier to type a 1 character alias than a full 20-30 character table name in your select and where conditions.

Greg.