Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 6th, 2011, 03:55 PM
Registered User
Points: 8, Level: 1
Points: 8, Level: 1 Points: 8, Level: 1 Points: 8, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2011
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default sql Update

Alias is not allowed in update in SQLSERVER?

Kindly let me know how to update this information..

update ctblworkorder A set (A.strInvoiceLink,A.strInvoiceLink) =
(Select b.ShipOrder,b.ShipVendor from ctblTempWrk001 b
where A.strSerialNumber=b.WOSerial and
A.strReceiveNumber=b.WORecvNo)
Where Exists ( Select 1 from ctblTempWrk001 c
Where A.strSerialNumber=c.WOSerial and
A.strReceiveNumber=c.WORecvNo and
A.strinvoicelink<>c.shiporder )

Can we not update like this in SQL SERVER???
 
Old October 7th, 2011, 02:43 AM
Registered User
Points: 3, Level: 1
Points: 3, Level: 1 Points: 3, Level: 1 Points: 3, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2011
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default sql Update

Hi,


Unfortunately SQL Server doesn't support aliases using the standard
ANSI SQL UPDATE syntax. Instead you have to use the FROM clause, which
is a proprietary extension:

UPDATE qi
SET avgcost =
(SELECT avgcost
FROM e_icitems AS ii
WHERE qi.item = ii.item)
FROM quoteitems AS qi;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
 
Old October 7th, 2011, 09:25 AM
Registered User
Points: 8, Level: 1
Points: 8, Level: 1 Points: 8, Level: 1 Points: 8, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2011
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Sql

Thanks for your reply. I accept for 1 field update this works. For Multiple fields eg.. I am trying to update File Temp003 from Fields in TEMP001

UPDATE TEMP003 SET (SHIPORDER,SHIPVENDOR) =
(SELECT SHIPORDER,SHIPVENDOR FROM ctblTempWrk001 AS TEMP001
Where TEMP003.WOSERIAL=TEMP001.WOSerial and TEMP003.WORecvNo=TEMP001.WORecvNo )
FROM ctbltempwrk003 AS TEMP003

Incorrect Syntax




Similar Threads
Thread Thread Starter Forum Replies Last Post
T-SQL Update Loop Gyto SQL Language 3 February 2nd, 2007 05:52 AM
C# Sql dynamic update N4th C# 2005 0 December 20th, 2006 09:05 AM
update sql kim Beginning VB 6 0 April 2nd, 2006 02:25 PM
SQL update records topshed Classic ASP Components 5 September 9th, 2003 09:48 AM
Update sql dhborchardt Classic ASP Databases 1 June 10th, 2003 10:06 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.