Select Query & Update Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Query & Update Query

Author

Topic

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2014-02-10 : 19:11:29

I am trying to get data into one table for a Crystal Report out of VS2010.

The first query I have is a select query.

SELECT     State, Country, COUNT(Region) AS CountOfRegion, Region
FROM         groups
WHERE     (Region BETWEEN N'01' AND N'09') AND (NOT (City IS NULL)) OR (City = N'-') 
GROUP BY State, Country, Region

Which returns data like this:
State, Country, CountOfRegion, Region
I was trying to put those results in a temp table called #cntus so I could update one of the records, like below.

The next part is my problem. One of the values returned is
State Country CountOfRegion Region
NULL CANADA: FRENCH-SPEAKING 6 06

Now the CountOfRegion is not correct for the Country = CANADA: FRENCH-SPEAKING and I need to get the correct amount from another table which does not have a PK. So I was trying to do a query like this.

Update #cntus 
(Select [CanadaFrenchSpeaking]
from settings)
SET CountOfRegion = [CanadaFrenchSpeaking]
Where Country = 'CANADA: FRENCH-SPEAKING'

Anyone know the proper way to do something like this? I have a whole bunch of reports to do and I will need to do a select query and then an update query to get the right data.

Thanks,
Stacy

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-02-10 : 19:15:52

How many rows are there in settings?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2014-02-10 : 19:18:05

Only one row in settings.

-Stacy

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-02-10 : 19:33:51

Update #cntus
SET CountOfRegion = (Select [CanadaFrenchSpeaking] from settings)
Where Country = 'CANADA: FRENCH-SPEAKING'

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2014-02-10 : 19:46:45

Thanks for your reply and help by the way.

It works! Thank you so much for your help. I was pulling my hair out.

-Stacy

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-02-10 : 19:57:20

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2014-02-10 : 21:22:43

One more question.
If I were trying to update several fields at once like below

Update #cntus 
SET CountOfRegion = (Select [GermanyGrps]
      ,[Austria]
      ,[Belgium]
      ,[France]
      ,[Liechtenstein]
      ,[Switzerland]
      ,[Japan] from settings)
Where Country = 'AUSTRIA'

How could I tell it that the GermanGrps goes with the Country = Germany in the other table and so on.

Thanks,
Stacy

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-02-11 : 12:04:22

You'll need to run multiple updates as this is a design issue. I would fix the design so that it allows you to do it one query. Your settings table would need to change so that the columns are data.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2014-02-11 : 17:43:28

quote:
Originally posted by tkizer

You'll need to run multiple updates as this is a design issue. I would fix the design so that it allows you to do it one query. Your settings table would need to change so that the columns are data.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

What do you mean by "so that the columns are data"? The settings table looks like this:
CREATE TABLE [dbo].[settings](
[SettingsID] [int] IDENTITY(1,1) NOT NULL,
[BegYear] [date] NULL,
[EndYear] [date] NULL,
[GermanyGrps] [float] NULL,
[Austria] [float] NULL,
[Belgium] [float] NULL,
[France] [float] NULL,
[Liechtenstein] [float] NULL,
[Switzerland] [float] NULL,
[Japan] [float] NULL,
[CanadaFrenchSpeaking] [float] NULL,
[StartDate] [date] NULL,
[StopDate] [date] NULL,
[WaitingMen] [float] NULL,
[WaitingWomen] [float] NULL,
[Region] [float] NULL,
[StPaultax] [decimal](18, 4) NULL,
[MNTax] [decimal](18, 4) NULL,
[Modified] [timestamp] NOT NULL

There is one row in the table with data entered. The numbers that I am trying to update in the data from my first query.

-Stacy

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-02-11 : 18:06:02

I mean that this table would now have multiple rows. You'd remove those country columns and each of those would be a row in the table. Then you could easily join to this table to do your update in one query.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2014-02-11 : 18:33:43

quote:
Originally posted by tkizer

I mean that this table would now have multiple rows. You'd remove those country columns and each of those would be a row in the table. Then you could easily join to this table to do your update in one query.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

OK got it. So if I make a table like this:

CREATE TABLE [dbo].[EASettings](
	[GroupCountsID] [int] IDENTITY(1,1) NOT NULL,
	[Country] [nchar](25) NULL,
	[Total] [nchar](10) NULL,
 CONSTRAINT [PK_EASettings] PRIMARY KEY CLUSTERED 
(
	[GroupCountsID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

I can join on Country then?

-Stacy

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-02-11 : 18:42:08

Yes something like that. I assume Total is the column you need for your update.

UPDATE c
SET CountOfRegion = e.Total
FROM #cntus c
JOIN EASettings e ON c.Country = e.Country

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2014-02-11 : 18:53:09

quote:
Originally posted by tkizer

Yes something like that. I assume Total is the column you need for your update.

UPDATE c
SET CountOfRegion = e.Total
FROM #cntus c
JOIN EASettings e ON c.Country = e.Country

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

YES it works!

Tara words cannot describe how much I appreciate your help with this. I was trying to use a CASE and all sorts of crazy things.
-Stacy

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-02-11 : 19:03:18

Woohoo! Glad to help.

Properly normalizing your database makes queries easier to write.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources