update a record in one table from copying a record in another table

hi,
Update a record in one table from copying a record in another table. i have a the following table structure in two tables.

table name: tempcategory & category

memid
memname
memdesc

now i want to copy memname and memdesc from tempcategory to category table where memid of tempcategory equals to memid of category


shiva

Try this:

MySQL >= 4.0.4 (see 13.1.10 online doc):

update category, tempcategory set category.memname = tempcategory.memname, category.memdesc = tempcategory.memdesc where category.memid=tempcategory.memid

Oracle:

update category c set (memname, memdesc) = (select memname, memdesc from tempcategory where id=c.id)

I happen to have a slightly more complicated case, I have the following tables

A. invoice_items, fields:
*item_id, description, unit_price

B. invoice_templates, fields:
*template_id, template_description

C. invoice_template_items, fields:
template_id, item_id, quantity

D. user_invoices, fields:
*invoice_id, user_id, template_id, invoice_description

E. user_invoice_items, fields:
invoice_id, item_id, unit_price, quantity, amount

* above mark auto_increment fields, in the last table, amount = unit_price * quantity

As you can see, the invoice_templates are predefined by the admin, each template with a unique id in table B, and details in table C.

What I need to do is that, the admin shall be able to issue a pre-defined invoice template to a specificed user, so he select a template_id and a user_id from the list, then, I do...
1. create a new record in table user_invoices, and get the new invoice_id
2. select all records from (invoice_template_items t LEFT JOIN invoice_items i ON t.item_id = i.item_id) where template_id = '<user specified template_id>', and insert these records into table user_invoice_items, each record with the new invoice_id generated in step 1; also calculate the new amount value

Previously I implemented step 2 throgh a for ... loop, i.e. read the records into an external array in PHP, then insert each array item one by one into table user_invoice_items. However I believe there must be some better way, preferably with just on SQL query.

Can you please help?

Why don't you simple insert them into your table?

INSERT INTO user_invoice_items SELECT new_invoice_id, wanted_columns FROM invoice_template_items LEFT JOIN ....

Thanks dude, but please note the invoice_id field is not in invoice_templates, or invoice_template_items, or invoice_items. Instead, it is the sql_last_id value for the latest inserted record in table user_invoices...

I think I have made my question too complicated. Sorry for that. What if we consider this case:

Table 1 with fields:
A, B, C

Table 2 with fields:
A, B, C, D

then, how can I copy all records from table 1 to table 2, and set each record with D = 1, using ONE sql query?

As mentioned.

Your INSERT looks just like

INSERT INTO table2 SELECT a,b,c,1 FROM table1

or, to be more precise

INSERT INTO table2(a,b,c,d) SELECT a,b,c,1 FROM table1

i have these tables:
installation (packid, tagnum, instcost)
machine (tagnum, compid, deptno)
depart (deptno, deptname, balance)

i'm trying to create a trigger so that for each new record inserted into installation,
the balance field in depart in increased by the instcost from installation.
the trigger seems to create fine, but testing it gives an error

ORA-04091: table S214856.INSTALLATION is mutating, trigger/function may not see it

create or replace trigger updeptbalance
before insert on installation
for each row
declare
v_instcost installation.instcost%type := :new.instcost;
v_tagnum machine.tagnum%type := :new.tagnum;
v_mdeptno machine.deptno%type;
begin
select deptno into v_mdeptno
from machine
where tagnum = v_tagnum;
update depart
set balance = balance + v_instcost
where deptno = v_mdeptno;
end;

Works fine on oracle 8.1.7.

The error that you've stated usually occurs if you select from the table that the trigger was written for.

Perhaps there's another trigger. Verify this:

SELECT trigger_name FROM user_triggers WHERE table_name='INSTALLATION'

If updeptbalance is the only trigger, I'd try to declare your variables without using %TYPE.

By the way: you can do what you want without declaring any variables and with just one update:

CREATE OR REPLACE TRIGGER updeptbalance BEFORE INSERT ON installation FOR EACH ROW
BEGIN
UPDATE depart SET balance = balance + :new.instcost WHERE deptno = (SELECT deptno FROM machine WHERE tagnum = :new.tagnum);
END;
/

thanks michlmann!!
u were right, there was another trigger (an earlier attempt!).
and i think i might use your code, its much neater.
thanks again.

Good Morning All,

I have been thinking and trying all different sort of things but nothing is seeming
to work.

I have the following problem:

I have two different tables (msAccess2000): TblA and TblB . In TblA, I have near 20 fields, In TblB, I have
a little less.

The situation is the following: I am trying to add one or more records (normally one) from TblA to a third
table, call it TblC. This should be done using a form which will display some of the Table fields (this form will only display
4 or 5 fields). The problem is that I should select with the mouse the record I want to add to the third table and by clicking
a command button the selected record will be inserted in that table. The logic should be the same apply from TblB to TblC but with the variant that it should not clear the records which were already added from the first table. In this second table normally
the records to add (still some fields, 4 or 5 compatible with the ones in the first form) will be more than one.

In the end I should be able to run a form on the third table and eventually print a report.

How can I do this. Can anyone help me? I must deliver this project early next week, and don't have any bright idea to do this.
I would apprecciate any help. Many thanks in advance.

K.

Please, would you be a little bit more specific/concrete.

I don't see the problem, even I don't understand what you want to do :-)

Are you talking about two separate tasks or...? - I don't know.

Hi, Michlann

Maybe I've failed to explain myself. Basically the problem
is like this:

I have 2 different tables with 3 or 4 compatible fields.
These 2 tables are linked by a field.
There is a third table, which is empty, in the beggining
with fields also compatible with this 3 or 4 in the upper
tables.
What I need to do:
I must create a form in which the user can select one record
from the first table; and several from the second one.
The fields shown will be only the compatibles (these 3 or 4 fields)
but one of then is Currency, and the form should display the sum
of the Field from the record in the first table and of the compatible fields
from the selected records from the second one.
How to do that?
I though in creating a third table in order to add this records and then in that
form I could add a total for instance.
The problem is I must do all this (the selection, and eventually the adding of records
using buttons so that the procedure can be done by
anyone, and the user doesn't need to know sql.
So, now you have it. I hope I showed you the picture. Can you give me a hint on how
can I do this?
To see it best, think of a CarStore. We have the cars, and we can add some extras it´s like this
We selected one record (for instance a car) and then we can add some records form the second
table (the extras), but allways remebering that these ones only aply to certain records of the first table
(is like saying not every car has the same extras).
Many thanks in advance.

K.

I only can give you a basic approach for access 95 with DAO but it might work with access 2000, too:

Take two listboxes, say lstCar and lstExtras.

Set the RowSource property of lstCars to

SELECT carId, ... FROM cars

and the BoundColumn property to 1.

Set the RowSource property of lstExtras to

SELECT extraId, ... FROM extras INNER JOIN cars ON extras.carId = cars.carId

and the BoundColumn property to 1.

Now define an AfterUpdate-event for lstCar:



Define an Click-event for your button:



Remember: this is just an untested idea, that might work with Access 95.

Hope it helps