May 17, 2005 at 3:52 am
Hi everyone,
I would like to know if the following UPDATE syntax is implemented (but not documented) in SQLServer 2000. This form is defined in ANSI SQL-2 standards.
UPDATE table
SET (col1,col2, ...) = (SELECT ...)
WHERE predicate
I know about the other syntax ...col1=exp1, col2=exp2... but I'm not interested in because if the database structure changes, I'll have to change the code as well.
Thanks,
May 17, 2005 at 7:02 am
Are you trying to use a subquery? Like:
UPDATE emp
SET deptno = 31
WHERE emp.deptno = (SELECT DISTINCT dept.deptno
FROM emp, dept
WHERE dept.deptno = emp.deptno
And dept.deptno = 30)
May 17, 2005 at 7:06 am
No, that syntax is not available in MS SQL Server 2000.
May 17, 2005 at 7:25 am
Hows this:
update titles
set pub_id =
(select pub_id from publishers
where pub_name = 'Godel Publishing')
where type = 'business'
May 17, 2005 at 7:42 am
This from an article on compatibility: with "SET ANSI_DEFAULTS ON" SQL Server adheres to the behavior that is compatible with ANSI standards
**ASCII stupid question, get a stupid ANSI !!!**
May 18, 2005 at 9:50 am
Try this:
UPDATE table
SET col1 = (SELECT ...), col2 = (SELECT ...)
WHERE predicate
Hope this helps.
Steve
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply