June 24, 2009 at 3:50 am
Hmm ok so maybe the title is a little obscure but I'll try to explain what I want to do.
I have 2 tables:
Table1 contains several columns and table 2 has some columns .
Table2 came in the model at a later point in time.
But Table 2 basically became a subset of Table1.
So table2 contains columns that are also in table1. Now I want to remove these columns from table one and just reference to the ID in table2 that contains these properties.
What's the best way to do this in T-SQL?
June 24, 2009 at 3:55 am
PLease provide us with some sample data..
Thanks.
June 24, 2009 at 3:58 am
This is what I was trying
UPDATE [Audit] as a
SET a.BatchID = b.Id
FROM Audit as a inner join Batch as b on a.BatchCode = b.Name
AND a.BestBeforeDate = b.BestBefore
AND a.ProductionDate = b.ProductionDate
//Edit
BatchCode, BestBeforeDate and ProductionDate are the mutual columns.
I need to retrieve the ID from the BatchTable and that ID should be set for the column BatchID in Audit
June 24, 2009 at 4:31 am
Hi
the_spoofer (6/24/2009)
Now I want to remove these columns from table one and just reference to the ID in table2 that contains these properties.
I don't understand the relation between this sentence and an UPDATE statement. If you want to remove those redundant columns and show data of both tables I would suggest to use a VIEW. If you want to update your data your statement looks fine.
Could you please help open my eyes? 🙂
How to Post Data/Code to get the best Help How to Post Performance Problems
June 24, 2009 at 4:45 am
The update returns an error on the first line
Incorrect syntax near the keyword 'as'.
But I don't want to show these columns anymore in the Audit table. In the Audit table I just want to see the ID of the batch (which used to be all the batch information in the Audit table)
June 24, 2009 at 4:51 am
the_spoofer (6/24/2009)
The update returns an error on the first lineIncorrect syntax near the keyword 'as'.
Oups... didn't read your statement correct. This should work:
UPDATE a
SET a.BatchID = b.Id
FROM Audit as a inner join Batch as b on a.BatchCode = b.Name
AND a.BestBeforeDate = b.BestBefore
AND a.ProductionDate = b.ProductionDate
You defined the alias "a" in your FROM clause, so you can use it in your UPDATE clause.
But I don't want to show these columns anymore in the Audit table. In the Audit table I just want to see the ID of the batch (which used to be all the batch information in the Audit table)
As I wrote above. So remove the columns. You can create a view which combines all data if needed.
How to Post Data/Code to get the best Help How to Post Performance Problems
June 24, 2009 at 4:55 am
Please try this as far as update statement is concerned.
UPDATE Audit
SET a.BatchID = b.Id
FROM Audit a inner join Batch b on a.BatchCode = b.Name
AND a.BestBeforeDate = b.BestBefore
AND a.ProductionDate = b.ProductionDate
As far as displaying the columns are concerned, you can create the view as mention above by Florian
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply