Very hard to see what you are doing with 'dummy data' like this, but for starters, like 'linno' said, in step 3 use UPDATE which will change the record instead of INSERT which will add a new record
I want to delete all the rows in the table after my last Insert. as i mentioned the table has 500 rows. last insert puts 300 more rows based on the joins of step 1 and 2. when the resulr=300 rows is obtained, i want to store it back to same table but deleting
the old rows.
I have achived this using temp and table variables. but i wnat to utilize just 1 table that is ABC.
DELETE FROM abc
WHERE abc.id NOT IN(
--will check your condition. If the id is not in the the collection
--returned, it will be deleted.
SELECT a.id
FROM abc a
--joining the table to itself
INNER JOIN abc b
ON a.somedate = b.somedata)
Make sure this is what you want before you run it as it will delete your data.
muhammadazee...
Member
4 Points
165 Posts
How to delete old values while inserting new one?
Dec 12, 2012 02:39 PM|LINK
Hi i have a 3 step process
1) i insert values in a table.
2) then i created a teporary table variable to store vales.
3) i need to perform Join on values obtained in step 1 and step 2. and then store new values in the same table while removing the old ones.
------------
I have written down the code but it add values to existing ones.
Step 1 results= 500
step 3 results = 300
I am getting 800, but i need the new set that is 300.
Thanks
limno
All-Star
117340 Points
8005 Posts
Moderator
MVP
Re: How to delete old values while inserting new one?
Dec 12, 2012 02:42 PM|LINK
You can use INNER JOIN to join tables first and UPDATE your table with the value you want.
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
msmk
Participant
776 Points
159 Posts
Re: How to delete old values while inserting new one?
Dec 12, 2012 02:43 PM|LINK
How are you inserting the new values (step 3)? Can you post the code?
muhammadazee...
Member
4 Points
165 Posts
Re: How to delete old values while inserting new one?
Dec 12, 2012 03:05 PM|LINK
----------------------------------Step-1---------------------------
DELETE FROM ABC
INSERT INTO ABC
SELECT CDE.A, EFJ.B
FROM CDE INNER JOIN
EFJ ON "some ID"
----------------------------------Step-2---------------------------
DECLARE @Temp_Table TABLE
(
[A] datetime
,[B] nvarchar(255)
,[C] nvarchar(255)
)
INSERT INTO @Temp_Table
SELECT XYZ.A, XYZ.B, XYZ.C
FROM XYZ INNER JOIN
EFJ ON "Some ID"
----------------------------------Step-3---------------------------
INSERT INTO ABC
SELECT DISTINCT
ABC.A, ABC.B, TT.C
FROM ABC LEFT OUTER JOIN
@Temp_Table TT ON "Some ID"
msmk
Participant
776 Points
159 Posts
Re: How to delete old values while inserting new one?
Dec 12, 2012 03:26 PM|LINK
Very hard to see what you are doing with 'dummy data' like this, but for starters, like 'linno' said, in step 3 use UPDATE which will change the record instead of INSERT which will add a new record
muhammadazee...
Member
4 Points
165 Posts
Re: How to delete old values while inserting new one?
Dec 12, 2012 04:14 PM|LINK
I want to delete all the rows in the table after my last Insert. as i mentioned the table has 500 rows. last insert puts 300 more rows based on the joins of step 1 and 2. when the resulr=300 rows is obtained, i want to store it back to same table but deleting the old rows.
I have achived this using temp and table variables. but i wnat to utilize just 1 table that is ABC.
THANKS
msmk
Participant
776 Points
159 Posts
Re: How to delete old values while inserting new one?
Dec 12, 2012 04:37 PM|LINK
Make sure this is what you want before you run it as it will delete your data.