I have two tables with identical structure such as
ID, product1, product2, product3, product4, product5

Table 1 is filled with 5 ID numbers, say 1,2,3,4,5 and NULL (ie nothing) for the products.

Table 2 is filled with 10 ID numbers, 1,2,3,4,5,6,7,8,9,10 and all product details filled in (doesnt matter what with).

I have to update Table 1 such that it basically becomes a copy of table 2 with all the details.

The question basically here is can this be done with a single query or must you use an UPDATE query to first update the details of the ID numbers 1-5, and then an INSERT query to get the details of ID's 6-10 into table one?
I have tried the UPDATE query, and managed to get it to update with ID's 1-5 with the product details, but did not manage to get it to insert ID's 6-10 into table 1.


"It doesn't matter what you think!"

Hi.

As far as I know, you need to use both update and insert.
For the insert you could try something like this:

INSERT INTO Table1(ID,product1,...)
SELECT ID, product1,...
FROM Table2 t2
WHERE NOT EXISTS
    (SELECT t1.ID FROM Table1 t1 WHERE t2.id = t1.id )

Not trying to be a smart-a$$ about this, but why don't you normalize your tables so you don't need nearly duplicate data in two separate tables? Also, having multiple products in the same row for a given ID is a form of denormalization that you can probably do without.

Of course, you may not be able to make such changes to the tables...

--Jeff Moden

yeah this is just an exercise so its not about normalising, but thanks for the suggestions, and yeah thanks Gert, that works perfectly.

"It doesn't matter what you think!"