3 Replies - 738 Views - Last Post: 16 July 2012 - 09:11 AM Rate Topic: -----

#1 itsjimmy91  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 77
  • Joined: 19-January 11

SQL Select within Select Help

Posted 16 July 2012 - 08:04 AM

Hey guys,

I've only done some minor things with SQL, so this task that I'm currently working on is stumping me a bit as it is more involved than anything I've worked with up until now.

I have a table where I have to select certain rows from the table based on a given ID and then set the values of ANOTHER row in the SAME table based off of a DIFFERENT ID equal to the values that were returned from the first given ID.

I'm not really sure how to go about doing this. Is this a Select within a Select type of problem? I haven't done any queries like this up until now and am not sure.

Thanks for any help.

Is This A Good Question/Topic? 0
  • +

Replies To: SQL Select within Select Help

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9207
  • View blog
  • Posts: 34,589
  • Joined: 12-June 08

Re: SQL Select within Select Help

Posted 16 July 2012 - 08:12 AM

Typically the 'in' keyword would work or a join on itself.

If I were you would I would start by breaking everything into temporary tables and working it that way.. worry about optimizing later!
Was This Post Helpful? 1
  • +
  • -

#3 itsjimmy91  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 77
  • Joined: 19-January 11

Re: SQL Select within Select Help

Posted 16 July 2012 - 08:26 AM

Hmmm...

I've started to throw together a couple of lines. I feel like I might be on track but I don't think I have it right yet.

Will something like this work?

declare @FromCompanyID int, @ToCompanyID int, @FromTable table
set @FromCompanyID = 10028
set @ToCompanyID = 10040
set @FromTable = (select * from ClientCompanyProfiles where CompanyID = @FromCompanyID)

update ClientCompanyProfiles
set ClientCompanyProfileID = @FromTable.ClientCompanyProfileID, CompanyID = @FromTable.CompanyID


Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9207
  • View blog
  • Posts: 34,589
  • Joined: 12-June 08

Re: SQL Select within Select Help

Posted 16 July 2012 - 09:11 AM

It's all about table aliases... In the code example I am using the same table twice, but feeding each alias a different where value.


SELECT lID
      ,sValue
      ,lA
      ,lB
  FROM dbo.foo_testk
  

lID	sValue	lA	lB
----------------------------
1	one	6	910
2	two	9	50
3	three	7	599
4	four	5	67
5	five	6	10
6	six	5	67




-- I really want to update the row from alias a.. aka id 6.. so I restrict the update for that alias... and I want to use values in lID =1 for the job.. so I restrict that alias..  in the background mssql is making temp tables for the the different bits of the update to make it all jive.. you just need to keep the alias straight and use a comma!
update a
SET 
a.lA = b.lA 
,a.lB = b.lB 
FROM foo_test a, foo_test b 
WHERE a.lID = 6  and b.lID = 1

select * from foo_test -- see the change!


lID	sValue	lA	lB
----------------------------
1	one	6	910
2	two	9	50
3	three	7	599
4	four	5	67
5	five	6	10
6	six	6	910


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1