Update Query - Syntax Error in Update Statement

[Nogi]

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?

 

[greekpatriott]

use a query choose the table you want to update. Choose update query. then in the "update to" write the table and the fields you want to be updated to.

This will give you the correct sql code.
Cheers

 

[Nogi]

that was a bit my problem. How do i define the "update to" fields.

I mean, i can add [fieldsomething] to the "update to" statement, but how do i write the tabledefinition?

Is it something like SortedFullExcelList.[somefield]?
Or [SortedFullExcelList].[somefield]?

Or how do you define something like that?

 

[greekpatriott]

choose the fields you wan to update then
you define the update to like this:
[tablename].[fieldname]
Try it and let me know how it goes.

 

[Nogi]

Yeay it works Greekpatriott!

*bows*

I only have one last question for you, cause it's not really clear to me:

the table and fields you type in the "update to"-part, are that the ones that are going to be updated? Or are that the ones you use to update the other table?

 

[greekpatriott]

update to are the fields that you want to have your table be updated to. So if it is not clear it is the ones you use to update the other table.
Glad to help.

 

[Nogi]

Ok greekpatriot, thanks for your help. Even though i thought the problem was solved, it seems it's not.

When i run the query, at every field i get an "enter parameter value" question. When i enter a value it replaces every field with that value.

Do you know why it asks for that value instead of using the value in the table it's supposed to update from?

 

[greekpatriott]

Hm
it seems that you did not type properly the update to field.
Did you use the brackets and the dot?
[].[]
[tablename].[fieldname]
type the table name that you want the table to be updated to
type the fieldname you want the field to be updated to.

Did you select the table you want to update. Did you double click the fields so that they are selected in the query?

Ok let me know if you followed these steps?

 

[Nogi]

I wonder...could it be because the fields aren't yet related to eachother? i mean, as in the use of relationships?

 

[greekpatriott]

greekpatriot@lycos.co.uk
Send me the two tables and I will do it for you
or post the sql code.

 

[PHV]

UPDATE SortedFullExcelList p INNER JOIN FullExcelList n ON p.id = n.id
SET p.Itemnumber = n.Itemnumber, p.ItemDescription = n.ItemDescription, p.ItemType = n.ItemType, p.Download = n.Download;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Nogi]

Thanks for your code PHV, and when i use it, i don't get any errors (yeay!!), BUT, somehow i think the wrong table is being updated. Could it be?

The idea is:

the fields of the table "SortedFullExcelList", are to be updated by the fields of table "FullExcelList".

When i add a new record in the FullExcelList, and let the query run with your piece of code, the added field doesn't show in the "SortedFullExcelList".

Could it be the script? Or something i did wrong?

 

[PHV]

Only the records of SortedFullExcelList having same id as records in FullExcelList are updated.
If FullExcelList contains records with id not in SortedFullExcelList then you have to consider an append query:
INSERT INTO SortedFullExcelList (id, Itemnumber, ItemDescription, ItemType, Download)
SELECT id, Itemnumber, ItemDescription, ItemType, Download
FROM FullExcelList
WHERE NOT id In (SELECT id FROM SortedFullExcelList);
Or maybe this:
INSERT INTO SortedFullExcelList (id, Itemnumber, ItemDescription, ItemType, Download)
SELECT n.id, n.Itemnumber, n.ItemDescription, n.ItemType, n.Download
FROM FullExcelList n LEFT JOIN SortedFullExcelList p ON n.id = p.id
WHERE p.id Is Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[greekpatriott]

Or,
Manually add the fields that you want to be updated, then just run the updatable query.

 

[Nogi]

ok, lemme see if i get this right:

If i use an "Append"-query as in your example PHV, then the records with the same ID are being skipped, and the records with a unique id are being added?

Or the other way around?

The easiest way to explain what i need is:

The fields of the SortedFullExcelList, are to be overwritten by the fields of the FullExcelList.

But, since since the fields of the SortedFullExcelList are in relationship with another table, i can't use the make-a-table-query, or something else that causes the table to be deleted and created again.

I hope this might have cleared some things up

 

[PHV]

The fields of the SortedFullExcelList, are to be overwritten by the fields of the FullExcelList
So no new record, right ?
The Update query is the good one in this case, provided the FullExcelList.id are the same as in SortedFullExcelList.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[greekpatriott]

I would choose the other way around.
ok let me explain.
If you want to update fields from a table from another table, then one field has to correspond to another field. It is simple. Dont think complexily. If there is another field that you may want to update in a non existant field then this field will be left blanc unless you add the field manually in the other table.

Ok thats about it. PHV can manipulate things in SQL, I dont claim to know SQL and I dont, though I can do simple things through the automation.

"PHV can you please give me a guidance in my threat I posted cheers".

 

[Nogi]

Auw i'm messing this up i think.

Darn, ether it's my bad english, or my confusing way of explaining things.

Look:

Table Shop
-----------
Fields: Sport , Food
Records Sport: football, Car
Records Food: empty

Table UpdatedShop
-----------------
Fields: Sport, Food
Records Sport: football, Car
Records Food: emtpy

So, in this case, when i add a record to the table "shop" in field Sport (let's say "hockey"), and i change the record "football" in "swimming"

I want the Table UpdatedShop to look like this after i ran the query:

Table UpdatedShop
-----------------
Fields: Sport, Food
Records Sport: swimming, Car, hockey
Records Food: empty

I hope that i managed to get it right now, lemme know if i still have not been clear somehow.

 

[PHV]

If you ADDED new id then the option is the Append query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Nogi]

and will the append query also change data of the existing records (as chaning the "football" into "swimming" as in my example?)