2

Good Morning,

I have a good look around for the answer but cant find it. Either that or the answer was there but I am thinking of it differently.

Basically, I have a large number of columns that I want to update from a select statement.

Looks something like this

DECLARE @StartDate as DATETIME;
DECLARE @EndDate as DATETIME;
DECLARE @Branches as VARCHAR(50);
DECLARE @Partcodelist VARCHAR(8000);

SELECT  @StartDate = (SELECT MIN(Date) FROM calendar WHERE WeekID = (SELECT WeekID FROM calendar WHERE Date = udf_CalculateDateShift(-56))), 
    @EndDate = udf_CalculateDateShift(-1),
    @Branches = '2000,2001,
    @Partcodelist = 'part1, part2';


UPDATE  dbo.Table T
SET a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p
FROM (select a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p from lots of other select sub queries) x

where t.date = x.date and t.branch = x.branch

Is that possible or do i have to do it some other way?

Thanks

Dave.

laylarenee
  • 3,276
  • 7
  • 32
  • 40
DJenkins
  • 31
  • 1
  • 1
  • 6
  • What errors are you getting? Can you create a SQL Fiddle example that gets the same error? That would give us more to go on. – SteveB Dec 30 '13 at 10:59
  • Also, look here for more assistance. http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server – SteveB Dec 30 '13 at 11:01

1 Answers1

1

It is possible indeed. Just make sure that the statement in the from clause is a select statement...you can use aliases if required, even joins, unions etc

Leo
  • 14,625
  • 2
  • 37
  • 55
  • Its returning Syntax errors on the ALIASES. I changed the be UPDATE SET T.a = X.a etc, I cant see any issues with the usual suspects of syntax errors like commas. – DJenkins Dec 30 '13 at 10:54
  • Syntax errors when you specify a table alias in the `from` clause? Please double-check because alias are indeed allowed in the `from` clause. Just make sure you don't alias the object (table) you are trying to update...the one following the `update` statement – Leo Dec 30 '13 at 11:28
  • Basically, if you do `update table1 set col1 = a.col2 from table2 a` you shouldn't have any problems with aliases – Leo Dec 30 '13 at 11:30
  • Cool, getting somewhere with this now thank you :) I removed the alias from the table that I am updating. However, it leaves my columns in the where clause as 'Ambiguous' - {WHERE Date = x.Date and TransbranchID = x.TransbranchID} how do you ambiguity with the the alias for the first table? – DJenkins Dec 30 '13 at 11:55
  • You need to uee a multi-part identifier. Use the following syntax....`tablename.columnname` – Leo Dec 30 '13 at 12:16
  • Ah - I see now! I had tried that but was putting the server and dbo in front of the table name which wasnt working - just the table name and the column name works! Leo the legend. Thank you for your help. – DJenkins Dec 30 '13 at 14:02
  • I marked it as the answer. However, I dont have enough reputation to vote the answer up as useful. Sorry! – DJenkins Dec 30 '13 at 14:03