Syntax error in update statement

[Nogi]

Hi all.

I'm a bit stuck on this problem i'm having with my update-query.

I've tried my best to write an sql query to update the fields of one table with the value of fields in another table, but i keep on receiving this annoying "syntax error in update statement".

Is there somebody who can help me out with this issue please?

This is what i've accomplished so far:

UPDATE SortedFullExcelList p
SET p.Itemnumber = (SELECT n.Itemnumber FROM FullExcelList n WHERE n.id = p.id),
SET p.ItemDescription = (SELECT n.ItemDescription FROM FullExcelList n WHERE n.id = p.id),
SET p.ItemType = (SELECT n.ItemType FROM FullExcelList n WHERE n.id = p.id),
SET p.Download = (SELECT n.Download FROM FullExcelList n WHERE n.id = p.id)

Uhm...any idea's?

 

[r937]

you're allowed only one SET keyword

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[jby1]

Try this instead:

UPDATE SortedFullExcelList p
SET p.Itemnumber = n.Itemnumber,
SET p.ItemDescription = n.ItemDescription, 
SET p.ItemType = n.ItemType, 
SET p.Download = n.Download 
FROM FullExcelList n 
WHERE n.id = p.id

 

[r937]

you're allowed only one SET keyword

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[checkai]

i don't think you can give your updated table an alias...

UPDATE SortedFullExcelList
SET ItemNumber =  (SELECT n.Itemnumber FROM FullExcelList n WHERE n.id = SortedFullExcelList.id),

 

[jby1]

you are right, only need SET once.

I don't have access to Query Analyzer at the moment, otherwise would have tested before posting

 

[jby1]

And of course you can't use an alias for the Updated table in the query!

Apart from those things, my suggestion should work

 

[Nogi]

ok, believe it or not, but i keep on receiving the same error checkai & jby1.

I've tried them both. Could it be there's it's something else?

 

[Nogi]

so uhm...in human language that means? lol

I'm not really a wizard when it comes to sql, so if you could explain it in newbie-kinda-way?

 

[TheBugSlayer]

Nogi,
try this:

UPDATE SortedFullExcelList
   SET Itemnumber = FullExcelList.Itemnumber,
   SET ItemDescription = FullExcelList.ItemDescription, 
   SET ItemType = FullExcelList.ItemType, 
   SET Download = FullExcelList.Download
FROM SortedFullExcelList, FullExcelList
WHERE SortedFullExcelList.ID = FullExcelList.ID

Do not use an alias with the columns you want to update, the engine knows which tables they belong to.

 

[jby1]

This is the fixed up version of my suggestion:

UPDATE SortedFullExcelList
SET SortedFullExcelList.Itemnumber = n.Itemnumber,
  SortedFullExcelList.ItemDescription = n.ItemDescription, 
  SortedFullExcelList.ItemType = n.ItemType, 
  SortedFullExcelList.Download = n.Download 
FROM FullExcelList n 
WHERE n.id = SortedFullExcelList.id

 

[r937]

TheBugSlayer, you are allowed only one SET keyword

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[TheBugSlayer]

Sorry Nogi, here it is again:

UPDATE SortedFullExcelList
   SET Itemnumber = FullExcelList.Itemnumber,
       ItemDescription = FullExcelList.ItemDescription, 
       ItemType = FullExcelList.ItemType, 
       Download = FullExcelList.Download
FROM SortedFullExcelList, FullExcelList
WHERE SortedFullExcelList.ID = FullExcelList.ID

 

[Nogi]

AaARGg! this is so frustrating..

I've tried both again. TheBugSlayer, i received the same syntax error at your script. Maybe it had to do with the SET word r937 was talking about.

Now jby1, when i tried yours i received a different kind of error (glad to see something else for a change though hehe)

it sais:

Syntax error(missing operator) in query expression 'n.Download FROM FullExcelList.n'

 

[r937]

update SortedFullExcelList 
   set Itemnumber = n.Itemnumber
     , ItemDescription = n.ItemDescription 
     , ItemType = n.ItemType 
     , Download = n.Download
  from SortedFullExcelList as p
inner
  join FullExcelList as n  
    on p.id = n.id

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[donutman]

TheBugSlayer, you may find this thread thread183-970523 interesting.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Nogi]

r937, thanks for your help bro, but now i get this error:

Syntax error(missing operator) in query expression 'n.Download from SortedFullExcelList as p
inner
join FullExcelList as n
on p.id = n.id

so wierd...

 

[SQLBill]

Do you have a typo in your query?

Your post has this:

'n.Download FROM FullExcelList.n'

jby1 has this:

n.Download FROM FullExcelList n

Notice the space between the table name and the alias in the last one. There is a period instead of a space in your version.

-SQLBill

 

[r937]

does the FullExcelList table actually have a column called Download?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[Nogi]

ok, don't kill me but uhm...i just found out i forgot to add the ID field in the SortedFullExcelList table.

Maybe that had caused the error..

Sorry people, i'm going to adjust that error and let you know if r937's script worked.

brb