How to perform complex insert, select, update in a single query?

Question

I have 2 tables - ACCOUNTS and USERS:

ACCOUNTS:

CREATE TABLE  `ACCOUNTS` (
  `pk` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` varchar(9) NOT NULL,
  `primary` varchar(1) NOT NULL DEFAULT 'N',
  `edit_on` bigint(10) unsigned DEFAULT NULL,
  `status` varchar(1) NOT NULL DEFAULT 'A',
  PRIMARY KEY (`pk`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=1234567890 DEFAULT CHARSET=latin1

USERS:

CREATE TABLE  `USERS` (
  `user_id` bigint(9) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

if edit_on contains a non-null value, it means the entry is an edit on an existing entry, whose pk is contained in edit_on.

Here's what I want to do:

1. For every user_id in ACCOUNTS, there should be a matching user_id in USERS.
2. If edit_on is not null, there should exist an entry in ACCOUNTS with it's pk equal to this (input) edit_on and user_id equal to this (input) user_id. And for that (old) entry, status should be set to E.
3. If primary = 'Y', then for other rows in ACCOUNTS with the particular user_id, primary should be set to 'N'

After experimenting a hell lot with different queries (using ON DUPLICATE UPDATE etc.), I still cant find a solution. Please help, as I've been working on this for 2 full days. Thanks in advance.

EDIT

Here's something along the lines of what i've tried so far:

insert into ACCOUNTS
case
    when edit_on is null
        then
            select null, '222222222', 'N', null)
    else
        select null, '222222222', 'N', pk)
        from ACCOUNTS
            where '222222222' = user_id and '2147483680' = pk
        select pk, '222222222', 'N', pk)
        on duplicate key update status='E'
end

I haven't done anything about primary yet PLUS this one's not the solution :D

Answer 1

It sounds like you need two constraints:

1) FOREIGN KEY constraint. This will ensure that any user_id inserted into ACCOUNTS exists in USERS.

ALTER TABLE ACCOUNTS
ADD FOREIGN KEY
accounts_user_user_id (user_id)
REFERENCES USERS (user_id)

2) Self-referencing FOREIGN KEY constraint. This will ensure that any value inserted into edit_on is a valid value from pk in ACCOUNTS.

ALTER TABLE ACCOUNTS
ADD FOREIGN KEY
accounts_accounts_edit_on (edit_on)
REFERENCES ACCOUNTS (pk)

Optionally, you can define what actions you want ON UPDATE and ON DELETE of the referenced key. You can read more about FOREIGN KEY constraints here.

You need to make sure that user_id on both tables is of the same type, and that both tables are InnoDB.