Insert two rows at the end on every...

[former_SQLA_member1694869]

I have a table that stores a structure. With the main material and component. The components are numbered.

Now I want to insert two components in the end of all components per main material in the table. These two new components are similar for all materials. How do I do this the best way?

Ex:

Material----Component----RowRn----
----------------------------------
Label2------EVO_121---------1-----
Label2------EVO_243---------2-----
Label2------EVO_638---------3-----
Label3------EVO_311---------1-----
Label3------EVO_120---------2-----
Label3------EVO_081---------3-----
Label3------EVO_987---------4-----


The two new components are NewComp1 and NewComp2
I want it to loo like this:
Material----Component----RowRn----
----------------------------------
Label2------EVO_121---------1-----
Label2------EVO_243---------2-----
Label2------EVO_638---------3-----
Label2------NewComp1--------4-----
Label2------NewComp2--------5-----
Label3------EVO_311---------1-----
Label3------EVO_120---------2-----
Label3------EVO_081---------3-----
Label3------EVO_987---------4-----
Label3------NewComp1--------5-----
Label3------NewComp2--------6-----

[Vlad]

As far as I know, there is no such record order in DB concepts. It depends on how you sort the table - it will give you different results. If you want to give positions to records, you need a field which you will use to sort records. When a new record is inserted, it must have the field value that will be the last when you sort this table.

[Breck_Carter]

The sentence "The components are numbered" seems to imply that RowRn is an existing column in the table. If that is true, your argument does not apply.

If that is NOT true, then you are correct... the rows are not sorted on Component so the problem is not solvable as shown.

[Vlad]

What do you think if, the select statement has the order by two columns: Material and RowRn, and in this case, if the record is inserted with RowRn values equal to 5 and 6, the required result can be achieved.

[former_SQLA_member1694869]

I want it to check on the last and second to last line if these two componenter there. On MAX (RowRn) and MAX (RowRn) -1.

[Breck_Carter]

Show us your code.

[former_SQLA_member1694869]

The only thing I have so far is (and your code):

  IF NOT EXISTS (SELECT material, component FROM component Where component = 'NewComp1' THEN...

[former_SQLA_member1694869]

I really need help

[Breck_Carter]

CREATE TABLE component (
   Material         VARCHAR ( 10 ),
   Component        VARCHAR ( 10 ),
   RowRn            INTEGER );

INSERT component VALUES ( 'Label2', 'EVO_121', 1 );
INSERT component VALUES ( 'Label2', 'EVO_243', 2 );
INSERT component VALUES ( 'Label2', 'EVO_638', 3 );
INSERT component VALUES ( 'Label3', 'EVO_311', 1 );
INSERT component VALUES ( 'Label3', 'EVO_120', 2 );
INSERT component VALUES ( 'Label3', 'EVO_081', 3 );
INSERT component VALUES ( 'Label3', 'EVO_987', 4 );
COMMIT;

INSERT component
SELECT Material, 'NewComp1' AS Component , MAX ( RowRn ) + 1 AS RowRn
  FROM component
 GROUP BY Material;

INSERT component
SELECT Material, 'NewComp2' AS Component , MAX ( RowRn ) + 1 AS RowRn
  FROM component
 GROUP BY Material;
COMMIT;

SELECT Material, Component, RowRn 
  FROM component
ORDER BY Material, RowRn;

Material   Component        RowRn 
---------- ---------- ----------- 
Label2     EVO_121              1 
Label2     EVO_243              2 
Label2     EVO_638              3 
Label2     NewComp1             4 
Label2     NewComp2             5 
Label3     EVO_311              1 
Label3     EVO_120              2 
Label3     EVO_081              3 
Label3     EVO_987              4 
Label3     NewComp1             5 
Label3     NewComp2             6 

[former_SQLA_member1694869]

Big thanks!

This is almost what I need. But I see I was unclear in my question. I want to insert these two lines in the table. How do I then?

[Breck_Carter]

The code is almost the same (see revised answer)... MAX plus 1 is now used for both INSERT statements because they are separate statements. Beware code like this, that it isn't accidentally rerun... either that, or add WHERE clauses to prevent problems if the NewComp rows already exist. Also understand that the SELECT part of an INSERT SELECT does not "see" any of the rows that the INSERT is inserting... I forget the technical word used to describe this behavior, but in the ancient language spoken by The People it is SQL-Anywhere-Does-Things-The-Way-They-Should-Be-Done.

[former_SQLA_member1694869]

You are right. I ought to put a check so not these two NewComp rows already exist on the last two rows. Then I have to do a check on 'NewComp1' = Component on line which is MAX (RowRn) and 'NewComp2' = Component on line MAX (RowRn) - 1. How do I add the check in your example

[Breck_Carter]

"Helping with your homework" is something I can do, "doing your homework" is something you must do 🙂

[former_SQLA_member1694869]

You have a point there, but I really do not know how to solve it... I now I can use: IF NOT EXISTS (SELECT material, component FROM component Where component = 'NewComp1' THEN... But I do not know how I can check on the last and second to last line if these two NewComp exists or not.

[former_SQLA_member1694869]

You have a point there, but I really do not know how to solve it... I now I can use:

IF NOT EXISTS (SELECT material, component FROM component Where component = 'NewComp1' THEN...


But I do not know how I can check on the last and second to last line if these two NewComp exists or not.

[former_SQLA_member1694869]

I'm stuck. I need help with the above.

[Former Member]

Why would you check if component='NewComp1' and component='NewComp2' are the second last and last row with respect to RowRn? Isn't it enough to check if the Components exist at all for the Material?

What would be the result if the components NewComp1 and/or NewComp2 exist for a Material? Would you add the components anyway? Would you move the component(s) to the end?

What would be the result if NewComp1 is the last one and NewComp2 is the second last one, means, the Components are last for the Material but not in correct order?

What is the primary key?

[former_SQLA_member1694869]

You are right, the only thing I need to check is whether these two new componenter already present in the structure of the respective materials. Do you have any idea on how I can do?

[Former Member]

First read this: http://dcx.sybase.com/1100/en/dbreference_en11/insert-statement.html

Then this MAY be a solution for you (it sorts existing NewCompN to the end):

insert into MaterialComponent 
on existing update 
select Material, 'NewComp1', 1+max(RowRn) 
  from MaterialComponent
 where Component not in ('NewComp1', 'NewComp2')
group by Material
;

insert into MaterialComponent 
on existing update 
select Material, 'NewComp2', 1+max(RowRn) 
  from MaterialComponent
 where Component <> 'NewComp2'
group by Material
;

commit
;

@Breck: Sorry, I know I must not do this, avoiding your attempt to bring this learner on the right path. But apparently a very strange variation of "Heartbleed" has attacked my mind so I coudn't resist. 😉

[Breck_Carter]

@Chris: Nice play on "heartbleed" 🙂

Without knowing what the primary key is, it's hard to tell how SQL Anywhere will know if a row is "existing" or not, and which column(s) will be subject to the "update" part of "on existing update"... maybe MERGE will help.

Plus testing... results can be shocking surprising when GROUP BY is involved.