September 22, 2016 at 4:47 pm
Hello,
There are Table 1 and Table 2
Table 1 - column A, B, C, D
Table 2 - column A, B1, C1, D1
I copied over the data to certain columns from table 1 to table 2
update Table 2
SET (B1, C1) = (SELECT B, C
FROM Table 1
WHERE Table 2.A =Table 1.A)
--> data copied successfully so now both data has same data
Here comes my question, now I have to remove the data from whatever I copied from table 1 to table 2 not delete the record. What should I do?
thanks.
September 22, 2016 at 4:53 pm
kennyhuang0108 (9/22/2016)
Hello,There are Table 1 and Table 2
Table 1 - column A, B, C, D
Table 2 - column A, B1, C1, D1
I copied over the data to certain columns from table 1 to table 2
update Table 2
SET (B1, C1) = (SELECT B, C
FROM Table 1
WHERE Table 2.A =Table 1.A)
--> data copied successfully so now both data has same data
Here comes my question, now I have to remove the data from whatever I copied from table 1 to table 2 not delete the record. What should I do?
thanks.
Restore the data from the temporary copy of Table2 that you created before you updated it. Because you knew you should have a way to rollback those changes.
But if you didn't do that you could get a copy of the last backup that would have the Table2 data as it was before you updated it. Restore it somewhere else outside of production and then restore the data back using the restored database.
Sue
September 22, 2016 at 5:09 pm
Thanks Sue.
I meant the script I did earlier was to copy the data from columns in table 1 to columns in table2.
Now both tables have the same data.
next step I have to delete the data in table 1 which I already copied to table 2.
What script should I use?
thank you.
September 22, 2016 at 6:01 pm
kennyhuang0108 (9/22/2016)
Thanks Sue.I meant the script I did earlier was to copy the data from columns in table 1 to columns in table2.
Now both tables have the same data.
next step I have to delete the data in table 1 which I already copied to table 2.
What script should I use?
thank you.
If both tables now have the same data, how would you know?
The only way I can think of is if you captured which rows were going to be changed before you do the update. Based on what you said defines the tables, there is nothing in the tables to tell you this so you would have to change how you are updating the data to first identify the rows in table 1 that you going to use to update table 2.
Sue
September 22, 2016 at 6:45 pm
kennyhuang0108 (9/22/2016)
Thanks Sue.I meant the script I did earlier was to copy the data from columns in table 1 to columns in table2.
Now both tables have the same data.
next step I have to delete the data in table 1 which I already copied to table 2.
Sorry...now I get what you mean with:
remove the data from whatever I copied from table 1 to table 2 not delete the record.
If you want to set the values you updated over in table 2 to NULL in table 1, you would just do the opposite update of table 1.
Your first update was (changed to using t-sql syntax):
update Table2
SET B1 = B, C1 = C
FROM Table1
WHERE Table2.ColumnA =Table1.ColumnA
So to set the values to null in table 1, the opposite would be:
update Table1
SET B = NULL, C = NULL
FROM Table2
WHERE Table2.ColumnA =Table1.ColumnA
Sue
September 26, 2016 at 11:42 am
Thank you so much Sue for the solution.
September 26, 2016 at 12:17 pm
Be careful when using those updates, they can be really bad. At least, that's what it's explained in here: http://www.sqlservercentral.com/articles/Performance+Tuning/62278/
To prevent the problem, always include your target table in the FROM clause.
update t2
SET B1 = B, C1 = C
FROM Table2 t2
JOIN Table1 t1 ON t2.ColumnA = t1.ColumnA;
update t1
SET B = NULL, C = NULL
FROM Table2 t2
JOIN Table1 t1 ON t2.ColumnA = t1.ColumnA;
September 26, 2016 at 3:29 pm
It's Oracle so it doesn't matter 🙂 See the original post, Oracle.
Sue
September 26, 2016 at 5:10 pm
Sue_H (9/26/2016)
It's Oracle so it doesn't matter 🙂 See the original post, Oracle.Sue
I don't see any reference to Oracle and this is a SQL Server site.
September 26, 2016 at 5:35 pm
Luis Cazares (9/26/2016)
Sue_H (9/26/2016)
It's Oracle so it doesn't matter 🙂 See the original post, Oracle.Sue
I don't see any reference to Oracle and this is a SQL Server site.
Must be my bad - I thought this from the original post was Oracle syntax:
update Table 2
SET (B1, C1) = (SELECT B, C
FROM Table 1
WHERE Table 2.A =Table 1.A)
Am aware of the ANSI standard use of subqueries to set the value of multiple columns from another table. Acceptability of this approach varies by platform and companies in many cases that I've seen.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply