Insert a duplicate copy of any record that meets certain criteria
-
Sorry for this dumb question but I am very new to SQL. Could someone help me write a SQL code?
I have MS SQL 2014 server.
I Have Table “A” with 10 columns. Column 9 has any of these four values: “Test”, “Hold”, “Go”, or “Flag”I would like to make a duplicate copy of any record where column 9 has a value = “Hold” or “Go” and change it to the value “Step 3”.
Thanks
- samn265 - Saturday, June 23, 2018 12:06 PM
INSERT INTO TableA(<ColumnList>)
SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, 'Step 3', Col10
FROM TableA
WHERE Column9 = 'hold'
OR Column0 = 'GO'You may also want to read the Stairway Series of articles on this site.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
If col1 is a primary key - how would you handle it?
-
Without a sample of the exact table structure, and some sample data, the answer is any number of ways.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
Thank you!
- samn265 - Saturday, June 23, 2018 12:06 PM
>> Sorry for this dumb question but I am very new to SQL. Could someone help me write a SQL code? <<
This is not a dumb question; it is a naïve question. If you were learning English for the first time and you had originally started in a language where everything is gendered, our lack of gendered nouns would drive you nuts; if you're from a Slavic language that doesn't have articles, this would be really weird. Or an Asian language that doesn't have plurals. Etc.
>> I Have Table “A†with 10 columns. Column 9 has any of these four values: “Testâ€, “Holdâ€, “Goâ€, or “Flag†<<
Let's start over. Obviously 'A' is a terrible name for a table. A table models either a relationship or set of entities and should have an appropriate name for that. Yes, I know you did this for posting, but please get in the habit of always thinking of a table is one of those two things and always using appropriate name, even in your postings. The next mistake, which is really fundamental, is it columns don't have positional numbers in RDBMS. We try to identify everything in this language that we can, by using names. Yes, SQL has some compromises that expand the list of column names in particular orders. Back in the 1980s when I was working on the SQL standards, we really didn't have much choice about this for to be practical. We builds the early SQL engines on top of existing filesystems. But that's another topic
What we wanted to see was something like this:
CREATE TABLE Foobars
( ....<< needs a key>>
foobar_status CHAR(4) NOT NULL
CHECK ('Test', 'Hold', 'Go','Flag' ),
..);You see how constraints limit the domain of a given value. We also have no idea what the key to this table is. If you truly have no idea whatsoever how RDBMS works, then start off with a copy of the "Manga Guide to Database"; everyone looks at me funny when I recommend this, but it is probably the simplest, most fun if you like Japanese comics, intro to RDBMS.
>> I would like to make a duplicate copy of any record [sic] where column 9 has a value = “Hold†or “Go†and change it to the value “Step 3â€.<<
Saying "make a duplicate copy" in SQL or RDBMS is like saying "let's eat fried babies!" To a vegan; it is wrong on so, so many levels. The whole you function of databases, not just SQL, was to remove redundancy not to create duplicates!
Why did you create this extra "step three" value as what I assumed was a possible status? I used to tell people it took about a year to learn SQL; I'm changing that to about a year to become nothing but a code monkey and about 3-5 years to actually become in RDBMS person. Stick with it, because frankly data is a lot more fun than just procedural code ever was! This is based on several decades of experience
Please post DDL and follow ANSI/ISO standards when asking for help.
-
Some people do not get that you didn't really name your table "A", and your columns "Col1, Col2.." Most of us understand that this was only a basic example.
There are some people on these threads that are like those acquaintances that every group of friends seem to have. They are not really your friend, and they just end up hanging out with you from time to time even though nobody really wants them around, and nobody really invited them. Every now and then they come up with a funny story, or or a good bottle of wine,, but most of the time they are a real pain.
As for your primary key issue, the question is what is your primary key? If it's an increasing number, then simply increment the value of the records.
If it's something else, than without some sample data and schema, it really is impossible to provide a goods answer.
The link below my signature will show you how to provide that.Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ - Michael L John - Saturday, June 23, 2018 12:50 PM
I am sure you meant column0 to mean Column9 / Col9 . Just dont want anyone to get confused
----------------------------------------------------
- samn265 - Saturday, June 23, 2018 4:57 PM
A primary key means the engine creates a new value there for you if you have the identity type of column.
In other words ... something like
Create table tableA
(
col1 int identity not null ,
col2 char(5)
)
means the col1 will auto populate. So you need to only handle the other columns.
So just do
insert into tableA(col2, col3, col4,col5,col6,col7,col8,col9,col10)
Select col2,col3, col4, col5, col6, col7, col8, 'Step 3', Col10
FROM TableA
WHERE Col9 = 'hold' OR col9 = 'go' /* is not case sensitive by default */----------------------------------------------------
- MMartin1 - Monday, June 25, 2018 8:59 PM
But to know better you can right click your table in SSMS and select script table as >Create to> clipboard , for instance, and paste it here so that we know how your table is defined. That is , if that is not a problem. Else if you dont want to include column name and structure information you can just include the relevant columns and alias them. As long as we have the general construct , then that is all we need.
If there is no identity on the primary key column then it takes a business rule to define what the col1 values for the new rows will be. That is the short answer. Usually it is an incrementing number but can also be a uniqueidentifier( a cryptic looking 32 bit hexa decimal value with hyphens).
----------------------------------------------------
- MMartin1 - Monday, June 25, 2018 9:05 PM
For starters, an identity is not necessarily the primary key. An identity and a PK are not related to each other. This is not a safe assumption.
If, however, your primary is an identity column, then MMartin1's advice will work.
Again, if you publish the schema of the table, we can point you in the right direction.Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ - samn265 - Saturday, June 23, 2018 12:06 PM
Something I should have mentioned but didn't, is a concept introduced in a short paper by Dr. Codd, the inventor of the relational model. It is called "degree of duplication" and deals with what would been commodity items; sets of identical, interchangeable entities.
.
This would be handled in SQL with a default clause that starts at one, has a check constraint to assure that it's always increasing, and is perhaps implemented (if appropriate) with a create sequence statement. This is a bit much to drop on someone who's just walking into the language, but it's worth learningPlease post DDL and follow ANSI/ISO standards when asking for help.
- samn265 - Saturday, June 23, 2018 12:06 PM
Coming back to your original post, there really isn't enough information to provide a correct solution. The simple reason is that if you run the code that might be given multiple times you will duplicate the same rows of data multiple times. What is really needed is DDL (CREATE TABLE statement) for the table involved, sample data (as INSERT statements) that is representative of the problem domain (i.e. not production data), and the expected results. If a record with "Hold" or "Go" has already been duplicated with column 9 set to "Step 3" do you want it duplicated again if the code is run a second (or multiple times) or are there other criteria that needs to be considered.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply