Databases
StackOverflow2013
Postsdbo
POStrange sql query behaviour
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POStrange sql query behaviour
    primarykey
    data
    text
    <p>I have two tables <code>Test1</code> and <code>Corr_table</code> </p> <p>Test1 table create script: </p> <pre><code>CREATE TABLE [dbo].[Test1]( [id] [bigint] NOT NULL, [Country] [varchar](3) NULL, [PeriodKey] [varchar](max) NULL, [a] [varchar](3) NULL, [b] [varchar](3) NULL, [c] [varchar](3) NULL ) </code></pre> <p><code>Test1</code> data:</p> <pre><code>id Country PeriodKey a b c 1 E 201201 1 5 9 1 E 201202 1 5 9 3 G 201203 3 7 11 4 H 201204 4 8 12 </code></pre> <p><code>Corr_table</code> create script: </p> <pre><code>CREATE TABLE [dbo].[corr_table]( [Country] [varchar](5) NULL, [id] [bigint] NULL, [Field] [varchar](10) NULL, [Value] [varchar](50) NULL, [Start_date] [varchar](50) NULL, [End_date] [varchar](50) NULL ) </code></pre> <p><code>corr_table</code> data:</p> <pre><code>Country id Field Value Start_date End_date E 1 a 4 201201 201202 E 1 b 6 201201 201202 </code></pre> <p>Now, if I write this query, </p> <pre><code>select a = case when x.Field = 'a' then x.value else a end, b = case when x.Field = 'b' then x.value else b end, y.* from dbo.Test1 y,dbo.corr_table x where y.id = 1 and y.Country = 'E' and y.PeriodKey in (201201) </code></pre> <p>It gives following result: </p> <pre><code>a b id Country PeriodKey a b c 4 5 1 E 201201 1 5 9 1 6 1 E 201201 1 5 9 </code></pre> <p>whereas i am expecting the below result:</p> <pre><code>a b id Country PeriodKey a b c 4 6 1 E 201201 1 5 9 </code></pre> <p>Why both columns is not updated in a single row? At once it’s updating only one column but should be updating both column</p> <p>i.e a should be 4, b should be 6 in a single row . But only updating one, why is that ?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMSU
    1. USMSU
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POStrange sql query behaviour
      1. This table or related slice is empty.
      1. VTDownMod
    2. VO
      singulars
      1. POStrange sql query behaviour
      1. This table or related slice is empty.
      1. VTUpMod
    1. COFor one, you're **not joining** your tables at all - you're just creating a cartesian product - is that really want you want? [Also, you should **stop using** the old-style JOIN syntax](http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/08/bad-habits-to-kick-using-old-style-joins.aspx) which can lead to errors like this - forgetting to provide any JOIN condition and thus creating a cartesian product
      singulars
      1. POStrange sql query behaviour
      1. USmarc_s
    2. COCan you show what result you expect?
      singulars
      1. POStrange sql query behaviour
      1. USAaron Bertrand
    3. COCan you explain your WHERE condition? I don't understand the filter criteria.
      singulars
      1. POStrange sql query behaviour
      1. USIain Samuel McLean Elder
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload