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?

 

[TheBugSlayer]

Thanks Donutman. It is true, but the WHERE still works. A JOIN will also give you a better performance if the joining fields are of a numerical type as opposed to text.

 

[Nogi]

ok, still the same error. Now even the rest of my stuff aint working anymore..

The little space difference is caused by the '-sign that the error assigned to the code in order to point to that piece of code.

It's not the script i've past in the sql-window, but however, i must say i'm stunned by your eye for details SQLBill!

r937, i've checked the fieldnames, and all is there in both tables, and correctly spelled..

It seems like this piece of code (together with my tables) is haunted or something..

 

[jby1]

Why don't you post the latest version of your query? There may be something simple that has crept in, and a fresh eye can work wonders!

 

[r937]

i tested my code and it works fine

create table SortedFullExcelList
( id smallint not null primary key
, Itemnumber char(3)
, ItemDescription char(3)
, ItemType char(3)
, Download char(3)
)
create table FullExcelList
( id smallint not null primary key
, Itemnumber char(3)
, ItemDescription char(3)
, ItemType char(3)
, Download char(3)
)
insert into SortedFullExcelList 
 values (1,'n-1','d-1','t-1','w-1')
insert into SortedFullExcelList 
 values (2,'n-2','d-2','t-2','w-2')
insert into SortedFullExcelList 
 values (3,'n-3','d-3','t-3','w-3')
insert into SortedFullExcelList 
 values (4,'n-4','d-4','t-4','w-4')

insert into FullExcelList 
 values (1,'nX1','dX1','tX1','wX1')
insert into FullExcelList 
 values (3,'nX3','dX3','tX3','wX3')

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
    
2 Row(s) affected 

select * from SortedFullExcelList

1  nX1  dX1  tX1  wX1
2  n-2  d-2  t-2  w-2
3  nX3  dX3  tX3  wX3
4  n-4  d-4  t-4  w-4

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

 

[Nogi]

ok jby1, maybe not a bad idea.

So far, this is what i have (what r937 posted):

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

 

[Nogi]

Mm...so it means it has got something to do with my field names or table names or something?

Cause if your piece of code works fine i assume it has got something to do with my tables, right?

 

[donutman]

You aliased the Update table so refer to it with the alias name in the Update line. I don't see anything else wrong, so maybe that's it.
-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]

 

[SQLBill]

Nogi,

What error do you get if you get rid of the alias'?

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

I know it's a pain typing all that, but let's try and see if we can 'rule out' the alias as being the problem.

-SQLBill

 

[SQLBill]

Nogi,

Gotta ask this....you posted this comment:

Now even the rest of my stuff aint working anymore..

What did you mean by that...is this script actually part of another script? Or is this all there is?

-SQLBill

 

[Nogi]

Goodmorning to all of you.
After a good night sleep, i hope i might find some new inspiration to share with all of you and make this aARrRg!-script work

Maybe to start with your question SQLBill. Maybe if i explain a few things you might get a better idea of what the intention of this all is.

1. I have an excel-sheet, of which i import values into an access table. The values of that excelsheet might change from time to time depending on a users' input, so the access table also changes every now and then. (this is the FullExcelList)

2. In the same database as the FullExcelList, i have another table with fixed values. No importation involved, just a plain and simple never to change table, calls PandSItems.

3. I was intending to make a query run, to create a new table (SortedFullExcelList) with the unique values off the FullExcelList. Cause in the FullExcelList there are some exact items showing up more then once, and i'd only like them to be displayed one time.

4. A Data Access Page would display the fields of the SortedFullExcelList.


Now, the problem was, since some of the fields of the SortedFullExcelList are in relationship with the fixed table in the database (PandSitems), i could not create a make-a-table query. Cause that would cause the table to be deleted and then created again, and since the fields were in relationship to the PandSItems, it was not allowed to be deleted.

So my intention was simply to create an update-query for the SortedFullExcelList instead of a make-a-table-query, so the relationships of the fields could keep on existing.

Now i hope i didn't make this too difficult, so if you'd still have questions i'd be glad to help you out understanding the intention.

The whole idea actually was to let it all run on a macro, so that whenever the database would be opened, somehow the excellist would be imported and the SortedFullExcelList would be updated.

When i said that the whole bunch didn't work, i meaned that when i ran the macro, everything got stuck (because of the error in the query), that caused the SortedFullExcelList not to work and the data access page not to work ether.

Now about the piece of code you gave me SQLBill, when i tried it, it gave me this error:

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

 

[jby1]

You are doing this in Access, not SQL Server?

If this is the case, perhaps you would get further by posting this question to an Access forum, not a SQL Server forum ;-)

 

[Nogi]

auw Darn.. i hate it when that happends...

I didn't even know there was a difference.
It might explain all of the errors, and the fact that you wizards didn't found an immediate solution to such an easy problem (or at least, that's what i thought it seemed).

Ok, i wish you people all the luck for next year, my best wishes, and my apologizes for having post this in the wrong forum, which must have caused you people headackes and probably lack of sleep..

kind & apologizing regards,

Nogi

 

[r937]

Access??!! this is Access??!!

dude, the UPDATE syntax is different for Access

i know what it is, too



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

 

[Nogi]

i've posted the question in the correct forum, and somebody is trying to help me out.

I think we'll get it managed, but thanks for offering your help.

I do have another question concerning queries, but since this aint the right forum i don't think i can ask this to you now.

But as i said, i can't thank you enough for having spend your spare time in my posting-mistake

Kind regards