This is somewhat related to my last question RE conflicting procedure
argument names, but in regards to multi-row inserts...
suppose the following procedure:
----
DROP PROCEDURE IF EXISTS sp_ImportedUpdate |
CREATE PROCEDURE sp_ImportedUpdate ()
DETERMINISTIC CONTAINS SQL MODIFIES SQL DATA
BEGIN
INSERT INTO Destination SELECT ID,
CONTACT_NAME,
CONTACT_EMAIL,
CONTACT_PHONE,
ADDRESS_1,
ADDRESS_2,
CITY,
PROVINCE,
POSTAL_CODE,
COUNTRY,
CIRCUIT_ID,
TIME_ZONE
FROM ToBeUpdated
ON DUPLICATE KEY UPDATE
ID = VALUES(ID),
CONTACT_NAME = VALUES(CONTACT_NAME),
CONTACT_EMAIL = VALUES(CONTACT_EMAIL),
CONTACT_PHONE = VALUES(CONTACT_PHONE),
ADDRESS_1 = VALUES(ADDRESS_1),
ADDRESS_2 = VALUES(ADDRESS_2),
CITY = VALUES(CITY),
PROVINCE = VALUES(PROVINCE),
POSTAL_CODE = VALUES(POSTAL_CODE),
COUNTRY = VALUES(COUNTRY),
CIRCUIT_ID = VALUES(CIRCUIT_ID),
TIME_ZONE = VALUES(TIME_ZONE);
END |
-----
Here, 'ToBeUpdated' is a view which highlights changes between to
tables with the same primary keys, but possibly different values in
other columns. The naming scheme here can be tricky, and on first
glance given previous problems, it would look to fail, because the
column name in VALUES() is the same as the one on the left. However,
this actually works as intended, where the "changed" values are the
ones being returned by VALUE. Can someone explain why this is? I'd
like to have a solid understanding of the scoping of procedure
variables which happen to be the same as column names.
thank you!
-lev
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
