watercanal: user_object.sql

# +--------------------+------------------+------+-----+---------+----------------+
# | Field              | Type             | Null | Key | Default | Extra          |
# +--------------------+------------------+------+-----+---------+----------------+
# | ID                 | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
# | UF_NUMBER          | int(18)          | YES  |     | NULL    |                |
# | UF_NAME            | text             | YES  |     | NULL    |                |
# | UF_ADDRESS         | text             | YES  |     | NULL    |                |
# | UF_IS_REGISTRATION | int(18)          | YES  |     | NULL    |                |
# | UF_IS_DELETED      | int(18)          | YES  |     | NULL    |                |
# | UF_USER_ID         | text             | YES  |     | NULL    |                |
# | UF_JOOMLA_ID       | text             | YES  |     | NULL    |                |
# | UF_UNIQUE          | text             | YES  |     | NULL    |                |
# +--------------------+------------------+------+-----+---------+----------------+

# +---------------------+--------------+------+-----+---------------------+----------------+
# | Field               | Type         | Null | Key | Default             | Extra          |
# +---------------------+--------------+------+-----+---------------------+----------------+
# | ID                  | int(11)      | NO   | PRI | NULL                | auto_increment |
# | UserID              | int(11)      | NO   | MUL | NULL                |                |
# | Number              | int(11)      | YES  |     | NULL                |                |
# | Name                | varchar(200) | YES  |     | NULL                |                |
# | Address             | varchar(200) | YES  |     | NULL                |                |
# | IsRegistrationExist | tinyint(1)   | YES  |     | NULL                |                |
# | IsDeleted           | tinyint(1)   | YES  |     | NULL                |                |
# | DateCreated         | datetime     | NO   |     | 2013-01-23 00:00:00 |                |
# | DateUpdated         | datetime     | YES  |     | NULL                |                |
# +---------------------+--------------+------+-----+---------------------+----------------+

UPDATE user_object
  INNER JOIN jm_user_object ON user_object.UF_JOOMLA_ID = jm_user_object.ID
  LEFT JOIN user_info       ON jm_user_object.UserID    = user_info.UF_JOOMLA_ID
SET
  user_object.UF_NUMBER          = jm_user_object.Number,
  user_object.UF_NAME            = jm_user_object.Name,
  user_object.UF_ADDRESS         = jm_user_object.Address,
  user_object.UF_IS_REGISTRATION = jm_user_object.IsRegistrationExist,
  user_object.UF_IS_DELETED      = jm_user_object.IsDeleted,
  user_object.UF_USER_ID         = user_info.UF_USER_ID,
  user_object.UF_JOOMLA_ID       = jm_user_object.ID
;

INSERT INTO user_object (
  UF_NUMBER,
  UF_NAME,
  UF_ADDRESS,
  UF_IS_REGISTRATION,
  UF_IS_DELETED,
  UF_USER_ID,
  UF_JOOMLA_ID
)
SELECT
  jm_user_object.Number,
  jm_user_object.Name,
  jm_user_object.Address,
  jm_user_object.IsRegistrationExist,
  jm_user_object.IsDeleted,
  user_info.UF_USER_ID,
  jm_user_object.ID
FROM jm_user_object
  LEFT JOIN user_info ON user_info.UF_JOOMLA_ID = jm_user_object.UserID
WHERE NOT EXISTS (
  SELECT NULL FROM user_object WHERE user_object.UF_JOOMLA_ID = jm_user_object.ID
);