Insert And Update In Same Query

[dev-ria]

Hello,

So i have query that takes data from table a and inserts into table b (which is empty) and updates field copied_date in table a.

 

so something like this

 

INSERT INTO `b`(`ba`,`bb`,`bc`) SELECT `aa,`,`ab`,`ac` FROM `a`';

UPDATE `a` SET `Copied_date` = 'NOW()';

 

I want to know how this query actually works and if I can simplify it even more. Does it INSERT INTO first of all the data then goes back and UPDATES? or does it work all in one query kind of way?

 

 

Thanks

Edited October 31, 2012 by dev-ria

[Christian F.]

The query works by reading the SELECT part first, then using the values returned as a multi-line INSERT statement. The MySQL manual should provide you with more details on this, if you need it.

 

As for how to simplify it: About the only thing I can see is to set the copied_date field to a timestamp, which updates on change. Again, the MySQL manual contains information on how to accomplish that.

[Muddy_Funster]

timestamp wouldn't make any difference, as it changes on update, not select. There is no more simple way to do the above, but a cleaner way would be to use a triger on table b that updates the copy_date of table a after each record is inserted. then you would only need to have the insert statement (but you would need to write the trigger in the database, so it would actualy total more code than you have already).

[nodirtyrockstar]

I think you want to use the 'ON DUPLICATE KEY UPDATE' clause. This is an add-on to the INSERT command which will update rows with matching primary keys, and insert when there is no match. (You will probably get a better response in the MYSQL thread.)

[dev-ria]

thanks guys for the info! how can i use ON DUPLICATE KEY UPDATE? what does it do exactly? because the table (table b ) am inserting into does not have any data so therefore would not have any duplicates. I looked at mysql dev but dont really understand what it's doing exactly.

Edited October 31, 2012 by dev-ria

[ManiacDan]

ON DUPLICATE KEY solves the question you seem to be asking in the thread TITLE, but has nothing to do with the thread's actual CONTENTS.

 

A trigger is the proper solution to this.

Edited October 31, 2012 by ManiacDan