June 17, 2009 at 5:40 am
I am attempting to write an update process in SQL 2005, this works fine in Access, where I can link the two tables prior to running the update. It does not seem to like SQL...
Update [ISD_Lease]
set isd_lease.[rstatus*] = [Remedy Import].[rstatus*] where isd_lease.[rstatus*] <> [Remedy Import].[rstatus*]
I have tried putting brackets around each segment of the field definition, it doesn't seem to like me...
the Access version looks like this:
Update dbo_ISD_Lease LEFT JOIN [dbo_Remedy Import] ON dbo_ISD_Lease.spconcat = [dbo_remedy Import].rconcat SET dbo_ISD_Lease.[rstatus*]=[dbo_Remedy Import]![rstatus*] where ((([dbo_ISD_Lease]![rstatus*])<>[dbo_Remedy Import]![rstatus*]));
it would be wonderful to just copy & paste this into SQL, since well, it is doing the exact same thing I want to do...
June 17, 2009 at 5:51 am
try this:
UPDATE isd
SET [rstatus*] = i.[rstatus*]
FROM dbo_ISD_Lease isd
LEFT JOIN [dbo_Remedy Import] i ON isd.spconcat = i.rconcat
WHERE isd.[rstatus*] != i.[rstatus*]
edit:
YOU MIGHT WANT TO PUT AN INNER JOIN THERE
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 17, 2009 at 5:52 am
Do your column names really contain "*" or are you about to create a old style LEFT JOIN?
Flo
June 17, 2009 at 6:15 am
Um, not sure what the i is for? was that shorthand?
June 17, 2009 at 6:16 am
Florian Reischl (6/17/2009)
Do your column names really contain "*" or are you about to create a old style LEFT JOIN?Flo
Yes, my columns really do have an * in them. that is how they came out of Remedy, and SQL didn't have an issue with the name, so we kept it for recognition sake.
June 17, 2009 at 6:17 am
i is the alias for your table it makes the code neater than rewriting the whole name of the table.
Sorry it's bad alias but you can change it if you want
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 17, 2009 at 6:18 am
Christopher Stobbs (6/17/2009)
try this:
UPDATE isd
SET [rstatus*] = i.[rstatus*]
FROM dbo_ISD_Lease isd
LEFT JOIN [dbo_Remedy Import] i ON isd.spconcat = i.rconcat
WHERE isd.[rstatus*] != i.[rstatus*]
edit:
YOU MIGHT WANT TO PUT AN INNER JOIN THERE
not sure if this would work in SQL 2005? I am trying to make this a stored procedure so it will run automatically with my other procedures, but I keep running into the "multi-part.." error.
June 17, 2009 at 6:20 am
Could you post the DDL (CREATE statements) for your tables "dbo_ISD_Lease" and "dbo_Remedy Import" please?
June 17, 2009 at 6:21 am
If my script doesn't work then one of the columns you are referencing is not in the table that it should be.
As requested above could you post the DDL please
EDIT:
Could you also post the fully error message please.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 17, 2009 at 7:30 am
Christopher Stobbs (6/17/2009)
If my script doesn't work then one of the columns you are referencing is not in the table that it should be.As requested above could you post the DDL please
EDIT:
Could you also post the fully error message please.
Much to my surprise, I copied your script (it looked like almost straight Access to me with the underlines), removed the underlines (dbo. instead of dbo_) and it worked...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply