Post reply

copy rows in the same table based on conditions

  • December 21, 2021 at 11:09 am

    #3965885

    Hi , no , there is nothing else , this is what I am wondering

    Unbenannt

  • December 21, 2021 at 11:10 am

    #3965887

    no there is is'nt anything at all

    Unbenannt

  • December 21, 2021 at 11:44 am

    #3965890

    Ok, please check table FREE04 for triggers.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • December 22, 2021 at 7:03 am

    #3966183

    Thanks , it was the trigger

  • December 22, 2021 at 7:25 am

    #3966187

    alig wrote:

    Thanks , it was the trigger

    Sounds like the trigger has been written in such a way that it accommodates only single-row INSERTs, which is a common problem. It needs a rewrite!

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • December 29, 2021 at 9:41 pm

    #3968260

    >> I have the table as in data below<<

    Please look at my credentials. This will let you know I have some authority when I tell you you're doing everything completely wrong. First of all, for the past 30+ years standard netiquette on SQL forums has been post in DDL and insert statements, not make people copy data over and over, row by row, from a spreadsheet. Spreadsheets do not behave like tables; spreadsheets do not have keys; spreadsheets use a completely different data model. But more than that, standard netiquette that you don't make people open attachments in a form of any kind. This is not just SQL! Are you the guy in the office who opens emails from strangers then doesn't understand why what's wrong malware got into his company computer?

    Unlike some other people here, I'm not willing to endanger my computer to help somebody for free. We you please quit being so rude? Do you know how to post DDL

    >>, now I would like to copy all of the rows in which TEXT1 is 'erledigt' and the DATE2 is within the year 2021, I would like to copy the rows within the same table and make sure to copy the same number of rows as they are in the table currently, for example for a TEXT2 Inspektion OLI PLUS, it occurs 12 times, so I would like to make sure that the it gets copied too 12 times. <<

    An SQL programmer would create a view off of a base table, not make copies. That's how we had to do with mag tapes and punchcards in the pre-relational days. You are literally 50 years behind the technology. But more than that, one of the fundamentals of any database, even the non-relational ones, is to reduce redundancy, not increase it. I see you use the old COBOL keyword OCCURS ; have you ever read anything by Dr. Codd about his degree of duplication attribute? We had to repeat copies of things back in the old days of punchcards (yes, I am not old and have worked with this technology).

    I see you like "column names" to make them look like a spreadsheet or an array in a procedural language. In SQL programmer would have given a name that implies exactly what that text contains.

    I also noticed that you are ignorant of the ISO 8601 standard, which the only date time format allowed in standard SQL. This is the expected yyyy-mm-dd layout that is acceptable to all SQL products and every ISO standard that uses the date.

    >> While I am copying I would like to set the new DAT>> 3 as 31.12.2021 [sic: 2021-12-31), also the part which confuses me is that there are FKs and PKs and all the records [sic: rows are not records!} have their own Ids [sic: record numbers are not keys] and I do not want the new rows to have old IDS. <<

    Identifiers are usually physical record locations. You're dumb enough to have used an identity column in your data, instead of a real key, you can get duplicates. Think of the identifier table property as being like the parking lot space numbers in a garage. There is nothing logically to keep that space from being used. Buying an automobile or truck or a motorcycle. This is why automobile manufacturers use the VIN. You have horrible fundamental concept errors.

    >> I have the following query and I was planning to use INSERT INTO .

    SELECT * FROM dbo.FREE04 -- array subscript and not a table name? What kind of entity is a "free"? Non-SQL programmers who are still back in spreadsheets or even BASIC tend to use meaningless short names like this.

    >> ... WHERE TEXT1='erledigt' AND TEXT2 like 'inspektion%' AND DATE2 BETWEEN '01-01-2021' AND '31-12-2021' ORDER BY DATE2 DESC; <<

    Why are you sorting a query result? 30 years ago, when we were setting up SQL, we wanted to have a tiered architecture. This meant that there would be a separate tier for display purposes, and sorting be done in that tier, not in the query. The order by clause converts things from a table to a cursor. Again you show your spreadsheet/array mindset by adding numbers to the ends of poorly named attributes. What exactly does a "DATE2" mean in a relational model? Something like, "customer_purchase_date" would be a better name for a column in an RDBMS..

    Everybody else's been very patient with you, but I would rather you get it right and learn how to be a real SQL programmer. If you keep posting to forums and have people do your work for you, you will become a dangerous liability to anyone that hires you because you won't be competent. If you want to try again and actually follow netiquette, and I'll be happy to play with your code. Hey, this is your chance to get some help from someone who actually invented the language and has written 10 books on it 🙂

    Please post DDL and follow ANSI/ISO standards when asking for help. 

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply