Update query simple (beginner)

[gphillips]

Hi I managed to create this update query in microsoft access with the below sql code:-

Two tables Des Ref and Des Ref2 are linked via Inner Join because I only want to update certain values in the Des Ref table. The Endate field in Des Ref table will be updated to 05/25/2007 where there is a match.

UPDATE [Des Ref] INNER JOIN [Des Ref2] ON [Des Ref].Desref = [Des Ref2].[Des ref] SET [Des Ref].Enddate = #5/25/2007#;

How do I do the same in SQL server 2000? Whats the simple code?

Cheers,

[Hack]

The only difference that I see is that SQL Server doesn't use the # to encapsulate dates.

Put your dates in single quotes, instead of the #, and run it in query analyser.

It should work just fine providing your SQL Server table structure mirrors your Access table structure.

[si_the_geek]

And just in case that doesn't work (I think this is one of those awkward cases that is different for each DBMS), here is an alternative version using a sub-query:

UPDATE [Des Ref] SET [Des Ref].Enddate = '5/25/2007'
WHERE [Des Ref].Desref IN (SELECT [Des ref] FROM [Des Ref2])

[gphillips]

Sorry for being a simpleton

I don't get this element in plain english i.e the in statement?

WHERE [Des Ref].Desref IN (SELECT [Des ref] FROM [Des Ref2])

[si_the_geek]

That's Ok, we all had to learn it once!

IN is a bit like = , except it allows any one of the values you specify. So if you wanted to check for the values 4 5 and 6, you could use this:

WHERE [Des Ref].Desref = 4 OR [Des Ref].Desref = 5 OR [Des Ref].Desref = 6

..or, you could change it to IN:

WHERE [Des Ref].Desref IN (4, 5, 6)

The nice thing is that you don't actually need to manually write the list of values... you can use a Select query to get them for you, as I did above.

If you juggle it around a bit, you can see that it is just an alternative way of writing the JOIN & ON sections from your original query (note that it is less efficient tho).

[gphillips]

The two tables are BFCD_Designs and USER_DESIGN_IMPORT. However, its not working. Whats wrong?

UPDATE  [BFCD_Designs] SET [BFCD_Designs].EndDt = 29/05/2007
WHERE   [BFCD_Designs].DesignID IN (SELECT [BFCD_Designs] FROM [User_design_import])

Its resulted in me updating all records in table but I have lost all my data. How do I get this back? I only wanted to updat the records where there was a match between designs - not every record in BFCD_DESIGNS.

[Ecniv]

UPDATE [BFCD_Designs]
SET [BFCD_Designs].EndDt = 29/05/2007
WHERE [BFCD_Designs].DesignID IN 
   (SELECT [BFCD_Designs] 
    FROM [User_design_import])

This would set the end date to 29 May 2007 for all records in BFCD_Designs where the BFCD_Designs are in the User_Design_Import.

You say it has updated all rows - I can only say that the data in user_deign_import matches all the rows on the main table.

If you use a select with a left join from the main table to the import table, do you get any import rows that are null (blank) ?
If so then the above query should have only end dated those that matched. Those that are blank shouldn't have been updated.
* This assumes bfcd_designs in the import table is the design id in the main table.

Run the left join select and see how many rows you are expecting to be updated. If any fall out as being null - check those in the main table to see when they were last updated.

If you are worried, and using Sql server, I think you can start a transaction before - then you can roll back if it is not right?
You may be able to use transactions in Access if you are use that.

Sorry I can't help more with it - sql server is something I couldn't get to run very well at home might have another go later on

[gphillips]

Cheers, thanks.

When I run the query I get the below error occurs.

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'BFCD_Designs'.

Re-Explanation of what I am tring to achieve.

Just to re-iterate, I simply want to update the BFCD_Designs table 'Enddt field with the date given. I only want to update where there is a match between User_D and BFCD_Designs on one field. Just to be clear the join is on DesignID field in BFCD_Designs table and Design field in User_D table. The User_D table only has one field where as the table to update has many fields.

UPDATE [BFCD_Designs]
SET [BFCD_Designs].EndDt = '29/05/2007'
WHERE [BFCD_Designs].DesignID IN 
   (SELECT [BFCD_Designs] 
    FROM [User_D])

Also , I think I might have to convert the date because the data type is date and time for EndDt field?? Default value is (9 / 19 / 2009). Not sure if this is important. I guess somthing like:....

CONVERT(DATETIME, '2999-09-19 00:00:00', 102))

last time I run it returned an incoorrect date??


Need an expert, some minor adjustments need to take place - would greatly apprecaite help.

Thanks.

[kaffenils]

Try this:

update [Des Ref] set EndDate='20070525'
from [Des Ref] inner join [Des Ref2] on [Des Ref].Desref=[Des Ref2].[Des ref]

[gphillips]

Hi thanks, I HAVE Chnaged the table names etc, its very confusing using des ref an des ref2 because the fields and table names are the same.Please Can you review using from last post, and also consider data and time and conversion if needed?? The data type from the end date is datetime.


You seem to have removed bracket references of tables as well.Also why '20070525' will this affect the format below??

In the visable table itself I need to update to just the date:

In the below format

30/05/2007


I can't change the datatype as it will affect a lot of other tables.

[si_the_geek]

Dates in SQL statements cannot be in dd/mm/yyyy format.. that will either give you errors, or the wrong data. You need to use mm/dd/yyyy or something like yyyy/mm/dd (as kaffenils did).

Also, as you want the 'join' to be on User_D.Design, that is the field that needs to be in the subquery, eg:

UPDATE [BFCD_Designs]
SET [BFCD_Designs].EndDt = '05/29/2007'
WHERE [BFCD_Designs].DesignID IN 
   (SELECT Design 
    FROM [User_D])

kaffenils may well have the right format for the proper Join, I use too many DBMS's to keep track of which is valid for which system.

[kaffenils]

Now I'm really, really, REALLY confused

Can you please tell me what you have renamed the tables and column to.
Also, is the column you want to update of datetime format?

[gphillips]

Just to re-iterate, I simply want to update the BFCD_Designs table 'Enddt field with the date 30/05/2007. I only want to update where there is a match between User_D and BFCD_Designs on one field. Just to be clear the join is on DesignID field in BFCD_Designs table and Design field in User_D table. The User_D table only has one field where as the table to update has many fields.

Two table:

BFCD_Designs - EndDt field to be updated (table to update)

USER_D - Used to only link to BFCD_Designs inner join to update only the records

Simon has different appraoch but seems to be on right lines in terms of understanding. You appraoch is slightly diffrent using a different technique.

Hope it clears it up. Appreaciate if you could calrify , there are few complication with dates and that.

I am jsut a beginner m8 ,sorry.

[gphillips]

Datetime is datatype, which cannot be changed.

need it visble

in the below format :

30/05/2007

Sorry. Just noticed didn't answer one of you questions.

[si_the_geek]

The data will be shown in whatever way you (or other programs) show it, but this still stands:

Dates in SQL statements cannot be in dd/mm/yyyy format.. that will either give you errors, or the wrong data. You need to use mm/dd/yyyy or something like yyyy/mm/dd (as kaffenils did).

[kaffenils]

This should be the correct syntax for SQL Server:

update BFCD_Designs set EndDt='20070529'
from BFCD_Designs t1 inner join User_D t2 on t1.DesignId=t2.Design

You can use the IN() example too if you want to.

The reason I'm using the format yyyymmdd is because this is the only format, and the ISO format, without any date separator characters. Let me put it this way; what date is 03/04/05? In U.S format that would be March 4 2005, in French/British it would be April 3 2005 and in Japan April 4 2003. See my point. So use the ISO format yyyymmdd.

[kaffenils]

Datetime is datatype, which cannot be changed.

need it visble

in the below format :

30/05/2007

SQL Server does not store the datetime value the way you see it. It is stored as two 4 byte integers. The first four bytes is the number of days after 01.01.1900 and the second four bytes is the number of 1/300 seconds after midnight. For displaying purposes you can format it as you wish.

[gphillips]

I notice there are no forward slashes in yOUR APPRAOCH.

Does this make a difference?

Does the data type being datetime make a diffence. Since I am picking up a database appliaction I didn't design some one used a
converting approach below fro a separate example.Although separte they are updating the same table!! Notice they use hyphens insetad of forward slashes below for dates and they also state time in the query.
Is there good reason 4 this?

UPDATE       BFCD_Designs
SET       BFCD_Designs.EndDt = '2006-11-21 00:00:00', BFCD_Designs.Notes = 'qty exceeded'
FROM         BFCD_Designs INNER JOIN
                      BFCD_Design_Equipment ON BFCD_Designs.DesignID = BFCD_Design_Equipment.DesignRef
WHERE     (BFCD_Design_Equipment.EBSRef = N'EBS-00846') AND (BFCD_Designs.EndDt = CONVERT(DATETIME, '2999-09-19 00:00:00', 102))
UPDATE       BFCD_Designs
SET       BFCD_Designs.EndDt = '2006-11-21 00:00:00', BFCD_Designs.Notes = 'EBS-000846 contract qty exceeded'
FROM         BFCD_Designs INNER JOIN
                      BFCD_Design_Equipment ON BFCD_Designs.DesignID = BFCD_Design_Equipment.DesignRef
WHERE     (BFCD_Design_Equipment.EBSRef = N'EBS-000846') AND (BFCD_Designs.EndDt = CONVERT(DATETIME, '2999-09-19 00:00:00', 102))

thanks for your kindness

[kaffenils]

SQL Server is capable of parsing different formats.
Take a look here http://msdn2.microsoft.com/en-us/library/ms187928.aspx
The reason I used yyyymmdd, without any slashes or hyphens, is that this formatting ALWAYS has year first, month second and day third. I have already explained the problems using slashes.

[gphillips]

Hi thanks for this code. Few questions:

Initial code(Hopefully SQL server 2000 compatible):

update BFCD_Designs set EndDt='20070529'
from BFCD_Designs t1 inner join User_D t2 on t1.DesignId=t2.Design

• Is t1 and t2 an alias. Is it exactky this code or do I need to take out t1 and t2?

1. Want to add to my knowldge - how do I change the set EndDt to equal the a particular field in the USER_D table after the join has taken place?
   i.e

   update BFCD_Designs set EndDt= User_D.Valtoadd.

   Please note valtoadd is not the primary key in the USER_D table. Its literally the next column across from the Design key.

• How do I add a condition to the initial code to show only update where the BFCD_Designs Enddt is equal to 19/09/2999?

[si_the_geek]

• Is t1 and t2 an alias. Is it exactky this code or do I need to take out t1 and t2?

That is correct.. anywhere that t1 is used, it actually refers to BFCD_Designs (and t2 is User_D). The main reason for using an Alias is to make the query shorter (thus easier to write), but can be useful in other situations (like using the same table twice in the same query).

The code looks fine as it is.

1. Want to add to my knowldge - how do I change the set EndDt to equal the a particular field in the USER_D table after the join has taken place?
   i.e

   update BFCD_Designs set EndDt= User_D.Valtoadd.

   Please note valtoadd is not the primary key in the USER_D table. Its literally the next column across from the Design key.

It doesn't matter which position the field is, the Join links all of the data in that row of the table - you can use any fields you like.

What you did was almost right, the only issue is that because there is an alias you cannot use the original table name - for anywhere outside of the From section, you need to use the alias instead, eg: ... set EndDt= t2.Valtoadd ...

• How do I add a condition to the initial code to show only update where the BFCD_Designs is equal to 19/09/2999?

You just add an appropriate Where clause to the end, eg: ...WHERE BFCD_Designs.EndDt = #09/19/2999#
(note the change of order - dates need to be in either mm/dd/yyyy or yyyy/mm/dd format)

I'm not sure, but you might need to change BFCD_Designs to t1

[gphillips]

Thanks very much, We could use this post as a progressive learning path if you don't mind - that maybe you could cut and paste for a tutorial if your interested - or I could start a sql lanuagae question and answer - guide from beginner level.

update BFCD_Designs set EndDt='20070529'
from BFCD_Designs t1 inner join User_D t2 on t1.DesignId=t2.Design

Hopefully all SQL server 2000 Compatable:

• Cheers, to answer EndDt field to valtoadd. I take the set needs to move position after the from. I take this is right?

update BFCD_Designs 
from BFCD_Designs t1 inner join User_D t2 on t1.DesignId=t2.Design
set t1.EndDt= t2.valtoadd

• Does it matter if the valtoadd is a date because it will will be visable as 29/05/2999. The rules on dates here may be slightlty diffrent because valtoadd is already within a table?

• Also if I wanted to add a mini but simple function to a where condition.
  I.e update where t1.revision is the maximum using the max function. How do I do this?

[si_the_geek]

Thanks very much, We could use this post as a progressive learning path if you don't mind - that maybe you could cut and paste for a tutorial if your interested - or I could start a sql lanuagae question and answer - guide from beginner level.

Actually we already have an SQL tutorial in the FAQs (our sister site www.sqlcourse.com ), so I don't think that it is worth creating another - tho I will add things to the FAQs if I think they are appropriate (and get the time to write them, or somebody else does!).

Hopefully all SQL server 2000 Compatable:

Unfortunately that is by no means guaranteed... the SQL syntax of Access is different to the SQL syntax of SQL Server (and then there is MySQL, Oracle, ...).

In general tho, learning the basic concepts is enough to be able to write queries in any DBMS - you just need to refer to the documentation to check what their syntax is.

I take the set needs to move position after the from. I take this is right?

No - the Set stays where it was, it needs to be before the From (and anything else).

set t1.EndDt= t2.valtoadd

Does it matter if the valtoadd is a date because it will will be visable as 29/05/2999. The rules on dates here may be slightlty diffrent because valtoadd is already within a table?

You can use values from wherever you like (hard-coded, a field, a database function, ...), as long as the query contains enough information to find the value - as you are using a field, this means the field must be in a table that is included in the query (either the one the Update refers to, or one in the From clause).

Note that the way the value is displayed is irrelevant - what matters is the data type of the field. As long as the data type of the two fields is the same it will work (if they are different, you may need to use a conversion function).

As an aside, dates are actually stored as numbers, at the time of posting this, the date/time is 39241.85388!

Also if I wanted to add a mini but simple function to a where condition.
I.e update where t1.revision is the maximum using the max function. How do I do this?

It's not particularly simple unfortunately.. you need to use a sub-query to find the value, using another alias (as you need to refer to the same table, but treat it as a separate one!).

The Where clause would be something like this:

WHERE t1.revision = (
           SELECT Max(t3.revision) 
           FROM BFCD_Designs t3
       )

..tho you presumably want to ensure that it is the maximum for just the current DesignId, in which case you need to add a Where to the sub-query, which would be done like this:

WHERE t1.revision = (
           SELECT Max(t3.revision) 
           FROM BFCD_Designs t3
           WHERE t3.DesignId = t1.DesignId
       )

[gphillips]

So this should work hey? If you like there are two mini filters before we update the BFCD_Designs table first the join between t1 BFCD_Designs and t2 USER_D then on these first filtered values create a sub query
where clause for the maximum revision on those filtered values in the BFCD_DESIGNS TABLE.

update BFCD_Designs set t1.EndDt = 20070530
from BFCD_Designs t1 inner join User_D t2 on t1.DesignId=t2.Design
WHERE t1.revision = (
           SELECT Max(t3.revision) 
           FROM BFCD_Designs t3
           WHERE t3.DesignId = t1.DesignId
       )

[si_the_geek]

There's only one thing that looks wrong there.. you have hard-coded a value for the date, but have not wrapped it in # signs - so it is a number, but much bigger than is appropriate (which will give you an error, or a date somewhere around the year 54887!).

Note that if you had used slashes (t1.EndDt = 2007/05/30) it would still be treated as a number - the result of the calculation (2007/5)/30 , which would actually be valid, but again would not be the date you wanted (in the year 0100?).

[gphillips]

Cheers, thnaks very much, will test monday.

• Is there a simple code to add plenty of space to an SQL query so its readable.Like if there was soem code in a table which you did not create and you want to break it up into managable pieces.

• Suppose I wanted to Update 2 fields in BFCD_Designs the same time. Would it just be - are there alternatives slicker ways if you could imagine there could be many i.e more than 2:

update BFCD_Designs set t1.EndDt = '20070530'
update BFCD_Designs set t1.qty = 3
from BFCD_Designs t1 inner join User_D t2 on t1.DesignId=t2.Design

• Could you even update one value in t2 and value in t1 at the same time?

update BFCD_Designs set t1.EndDt = '20070530'
update USER_D set t2.contract = 'Over supply'
from BFCD_Designs t1 inner join User_D t2 on t1.DesignId=t2.Design

[si_the_geek]

Is there a simple code to add plenty of space to an SQL query so its readable.Like if there was soem code in a table which you did not create and you want to break it up into managable pieces.

I have never found a "simple" way, and have generally done it by hand myself... but as it is something that we need to do regularly for forums questions, I have written a VB app to do it for for Select statements (haven't had the time/inclination to extend it), see the link in my signature for a link to the code.

Note that if you save your formatted query as an Access query, it will automatically remove any space, as Access re-formats everything to its own style.

Suppose I wanted to Update 2 fields in BFCD_Designs the same time. Would it just be - are there alternatives slicker ways if you could imagine there could be many i.e more than 2:

update BFCD_Designs set t1.EndDt = '20070530'
update BFCD_Designs set t1.qty = 3
from BFCD_Designs t1 inner join User_D t2 on t1.DesignId=t2.Design

You can use as many as you like, as the (short) tutorials at SQLCourse show. Note that you got the format wrong - you do not need to repeate the Update or the Set, you just add field & values separated by commas.

Could you even update one value in t2 and value in t1 at the same time?

There may be some DBMSs that allow this, but not the usual ones - so you'll need to run two completely separate Update queries.