SQL Update Statement Syntax Help 
Author Message
 SQL Update Statement Syntax Help
I have an Update Statement:

Update dbo_WORKSTAT
set name = directory.name,
location = directory.room,
phone = directory.phone,
emailaddr = directory.login,
dept = directory.dept
from directory
where directory.name = dbo_WORKSTAT.name;

and I receive an error message that something is wrong between dept =
directory.dept and from directory (lines 6 & 7).  Can someone please
help me?

Thanks!

-Brad



Sun, 24 Jul 2005 13:40:42 GMT
 SQL Update Statement Syntax Help

It is usually helpful if you include what the actual error is. A script
that reproduces the problem would even be more helpful - create table
statements, inserts, and your select.

        -Tom.



Quote:
> I have an Update Statement:

> Update dbo_WORKSTAT
> set name = directory.name,
> location = directory.room,
> phone = directory.phone,
> emailaddr = directory.login,
> dept = directory.dept
> from directory
> where directory.name = dbo_WORKSTAT.name;

> and I receive an error message that something is wrong between dept =
> directory.dept and from directory (lines 6 & 7).  Can someone please
> help me?

> Thanks!

> -Brad



Sun, 24 Jul 2005 13:52:52 GMT
 SQL Update Statement Syntax Help
Brad,

Please post the exact text of the error message.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:
> I have an Update Statement:

> Update dbo_WORKSTAT
> set name = directory.name,
> location = directory.room,
> phone = directory.phone,
> emailaddr = directory.login,
> dept = directory.dept
> from directory
> where directory.name = dbo_WORKSTAT.name;

> and I receive an error message that something is wrong between dept =
> directory.dept and from directory (lines 6 & 7).  Can someone please
> help me?

> Thanks!

> -Brad



Sun, 24 Jul 2005 13:55:35 GMT
 SQL Update Statement Syntax Help
Your are trying to update a table based on the contents of a join with
another table, therefore you must include the table to update in the from
clause:

UPDATE titles
   SET ytd_sales = titles.ytd_sales + sales.qty
      FROM titles, sales
         WHERE titles.title_id = sales.title_id



Quote:
> I have an Update Statement:

> Update dbo_WORKSTAT
> set name = directory.name,
> location = directory.room,
> phone = directory.phone,
> emailaddr = directory.login,
> dept = directory.dept
> from directory
> where directory.name = dbo_WORKSTAT.name;

> and I receive an error message that something is wrong between dept =
> directory.dept and from directory (lines 6 & 7).  Can someone please
> help me?

> Thanks!

> -Brad



Sun, 24 Jul 2005 14:26:09 GMT
 SQL Update Statement Syntax Help
Carlos,

Not true. SQL Server does NOT require that one repeat the name of the table
in the FROM clause.
Simple repro script to prove this:

create table Carlos1
(
 PK1 int NOT NULL primary key,
 c1 int NOT NULL
)

create table Carlos2
(
 PK2 int NOT NULL primary key,
 c2 int NOT NULL
)
go

insert into Carlos1 values (1, 10)
insert into Carlos2 values (1, 20)

update Carlos1
  set c1 = c1 + Carlos2.c2
from Carlos2
where Carlos1.PK1 = Carlos2.PK2

select * from Carlos1

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.



Quote:
> Your are trying to update a table based on the contents of a join with
> another table, therefore you must include the table to update in the from
> clause:

> UPDATE titles
>    SET ytd_sales = titles.ytd_sales + sales.qty
>       FROM titles, sales
>          WHERE titles.title_id = sales.title_id



> > I have an Update Statement:

> > Update dbo_WORKSTAT
> > set name = directory.name,
> > location = directory.room,
> > phone = directory.phone,
> > emailaddr = directory.login,
> > dept = directory.dept
> > from directory
> > where directory.name = dbo_WORKSTAT.name;

> > and I receive an error message that something is wrong between dept =
> > directory.dept and from directory (lines 6 & 7).  Can someone please
> > help me?

> > Thanks!

> > -Brad



Sun, 24 Jul 2005 14:50:31 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Help with SQL update statement syntax

2. Need help with Syntax on Update or Insert SQL Statement

3. Update statement syntax -- Please Help!!

4. Need help with a simple SQL statement (syntax)

5. Access 2000 Syntax error SQL Statement help

6. Syntax error in UPDATE statement

7. Syntax error in UPDATE statement while using a Memo field

8. Syntax error in UPDATE statement

9. Syntax Error in Update Statement

10. NEWBIE: easy syntax question with UPDATE statement

11. (0x80040E14) Syntax error in UPDATE statement.

12. UPDATE statement syntax


 
Powered by phpBB® Forum Software