Updating multiple fields mysql
You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. My Sql = "UPDATE Kontingent SET(Jan, Feb, Mar, Apr, Maj, Jun, Jul, Aug, Sep, Okt, Nov, Dec) = (2,2,1,1,1,1,1,2,2,2,2,2) WHERE Mem ID =18" I am updating another table before this one and it works fine, but when I excecute the statement. UPDATE Kontingent SET Jan = 2, Feb = 2, Mar = 1 WHERE Mem ID = 18 By the way - what database are you using, and are all of your fields of type INT? The syntax in this case would look like the following: UPDATE TABLE "table_name" SET ("column_1", "column_2") = ([new value 1], [new value 2]) WHERE " Ha en forsat god dag. My logical sence tell me that it can't be true that one have to send multiple commands to update one record. The syntax I'm suggesting doesn't mean that you have to send multiple commands - you can update many fields using one statement...But if you really want to take the trigger approach, that looks something like this: OLD.packaging_type) THEN BEGIN DECLARE my_width INT DEFAULT NULL; -- using DECLARE my_height INT DEFAULT NULL; -- the DECLARE my_weight INT DEFAULT NULL; -- appropriate DECLARE my_case_count INT DEFAULT NULL; -- data types here SELECT width, height, weight, case_count FROM PACK_TYPES WHERE id = NEW.packaging_type INTO my_width, my_height, my_weight, my_case_count; SET NEW.width = my_width, NEW.height = my_height, NEW.weight = my_weight, NEW.case_count = my_case_count; END; END IF; END $$ DELIMITER ; but it seems optimal to avoid that work until we know we actually need to execute the inner logic in the first place, which is avoided whenever 'packaging_type' hasn't actually changed on a row for a given update query.
Firstly, it needs the name of the table that is to be updated.But it's hard to tell from what has been explicitly given in your question.CREATE ALGORITHM=MERGE VIEW products_with_packaging_info AS SELECT p.*, pt.width as packaging_width, pt.height as packaging_height, pt.weight as packaging_weight, pt.case_count AS packaging_case_count FROM PRODUCTS p JOIN PACK_TYPES pt ON = p.packaging_type; queries against this view work exactly the same as queries against either table individually, as long as every product has a pack type.I offer this suggestion because a well-designed database should be such that it is impossible to get two different answers to the same question.
For example, if a PACK_TYPES row is changed because an error is found, how do its new values propagate backwards into products?
But if there are a large number of rows that require an update, then the overhead of issuing large numbers of UPDATE statements can result in the operation as a whole taking a long time to complete.