On Oct 18, 1:22 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 18 Oct, 11:21, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
> > On 18 Oct, 10:28, fmardini <f.mard...@gmail.com> wrote:
>
> > > I think I might have found a bug with insert select when inserting
> > > into the same table and using a composite primary key
> > > I made the smallest example I could come up with
>
> > > CREATE TABLE `t1` (
> > > `id1` int(11) NOT NULL,
> > > `id2` int(11) NOT NULL,
> > > `val1` int(11) default '0',
> > > `val2` int(11) default '0',
> > > PRIMARY KEY (`id1`,`id2`)
> > > ) ENGINE=InnoDB DEFAULT CHARSET=utf8
>
> > > CREATE VIEW t1v AS SELECT * FROM t1
>
> > > INSERT INTO t1 (`id1`, `id2`, `val1`, `val2`) VALUES (1, 1, 1, 10),
> > > (1, 2, 2, 20), (1, 3, 3, 30), (2, 1, 1, 40), (2, 2, 2, 50), (2, 3, 3,
> > > 60)
>
> > > I want to insert into the table from itself
> > > I use the view as a way to reference the selected row as opposed to
> > > the found (duplicate) row (otherwise i get an ambiguous reference
> > > error)
>
> > > INSERT INTO t1
> > > SELECT 1, id2, 0, 100
> > > FROM t1v WHERE id1 = 2
> > > ON DUPLICATE KEY UPDATE t1.val2 = t1v.val1 + 100
>
> > > After executing the query, the contents of the table are
>
> > > 1 1 1 103
> > > 1 2 2 103
> > > 1 3 3 103
> > > 2 1 1 40
> > > 2 2 2 50
> > > 2 3 3 60
>
> > > I was expecting the following
>
> > > 1 1 1 101
> > > 1 2 2 102
> > > 1 3 3 103
> > > 2 1 1 40
> > > 2 2 2 50
> > > 2 3 3 60
>
> > > Am I doing something wrong or is this a bug with INSERT SELECT
> > > If this is the wrong list, please tell me where to post
>
> > > thanks
> > > fmardini
>
> > Actually, the more I look at this, the more confused I become.
>
> > What are you trying to achieve here? Are you just wanting to update
> > val2 (where id1 = 1) to (val1 where id1 = 2) + 100?
>
> > If so wouldn't a multi-table update with aliases be easier?- Hide quoted text -
>
> > - Show quoted text -
>
> Since the val1 values for id1 = 2 are the same as those for id1 = 1,
> it is not at all clear.
>
> Please re-hash your sample data to make it clear what is required to
> happen.

Ok this table is supposed to track a relation between two kinds of
objects, and the composite primary key is made up of the ids of the
two related objects
What i am trying to do is create a relation between object1 and all
objects object2 is related to, but if the relation already exists i
have to update some parameters in the relation (the on duplicate key
thing)