The SQL Thread

• b3n3tt3 on February 25, 2013 10:09 AM

  siguro yung pinaka gusto mong mangyari eh naka sum ang bonus per employee para distinct talaga sa salary?

• hallie on February 25, 2013 10:23 AM

  @Web
  
  

  
  ;WITH cte
  AS
  (
  SELECT
  ROW_NUMBER() OVER(PARTITION BY Emp ORDER BY Emp) AS Num,
  Emp,
  Bonus
  FROM EmpBonus
  )
  SELECT
  a.Emp,
  CASE WHEN Num > 1 THEN NULL ELSE Salary END AS Salary,
  b.Bonus
  FROM Emp a
  LEFT JOIN cte b ON a.Emp = b.Emp
  

  
  
  -- edited by hallie on Feb 25 2013, 10:29 AM

• WebGuru on February 25, 2013 12:30 PM

  ayun nadale ni hallie!
  
  Thanks!
  
  medyo complicated kasi ung tunay na tables na ginagamit ko kaya yan ung sinample kong tables. ang kelangan ko na lang ngayon e iimplement to sa oracle mapping.
  
  Salamat ulit hallie!

• frankg2 on February 27, 2013 04:31 PM

  Patulong po. Self-study lang po sa sql
  
  I have 2 tables: A and B
  I want to update B.till to 100 where A.id = B.id
  
  First trial
  UPDATE B
      SET B.till = 100
  FROM B
      JOIN A
      ON A.id = B.id
  
  Second trial
  UPDATE B
      SET B.till =100
  FROM A, B
  WHERE A.id = B.id
  
  Ang error sa both trial ay
  Invalid Use of keyword
  Token: FROM
  Line Number: 3
  
  Any advice po?
  The database is Paradox Corel

• Kaoru on February 27, 2013 05:14 PM

  UPDATE A, B
  SET B.till =100
  WHERE A.id = B.id
  
  or
  
  UPDATE B
  SET till = (select decode(A.id, IS NOT NULL, 100, b.till) from A where B.id = A.id)
  
  di ko alam, Oracle, TSQL at MySQL lang alam ko pero you can try
  
  -- edited by Kaoru on Feb 27 2013, 05:27 PM

• jefoy1101 on February 27, 2013 05:17 PM

  you should not use FROM in an UPDATE statement this question can surely be answer by google reality bites see the link <click here for link>
  
  -- edited by jefoy1101 on Feb 27 2013, 05:34 PM

• Kaoru on February 27, 2013 05:20 PM

  10 bucks says i can google a page where you can use a FROM clause inside an update statement depending on which DB platform you're using

• arthurjr07 on February 28, 2013 08:15 AM

  sa t-sql
  
  pwede yung from sa update statement
  
  http://msdn.microsoft.com/en-us//library/ms177523.aspx

• hallie on February 28, 2013 09:47 AM

  @frankg2
  
  Try to create an alias to the table you want to update. I usually do it like this:
  
  

  
  UPDATE b
  SET till = 100
  FROM <TableB> AS b
  LEFT JOIN TableA AS a ON b.id = a.id
  WHERE ......
  

  
  
  or try to put "" on your field name.
  
  

  
  UPDATE B
  SET B."till" = 100
  FROM B
  LEFT JOIN A ON B.id = A.id
  WHERE ......
  

  
  
  -- edited by hallie on Feb 28 2013, 10:06 AM

• frankg2 on February 28, 2013 12:28 PM

  Sa 2 trials ko, apparently the Local SQL dialect I am using, it does not support Update when there are 2 tables joined :(
  
  Thanks sa inyong lahat
  
  Btw, sa mysql users, can it attach Paradox tables?

• olops0000 on February 28, 2013 04:42 PM

  @frankg2
  
  UPDATE B
  SET B.till =100
  WHERE B.id IN (SELECT A.id FROM A);

• stapo34 on March 08, 2013 10:46 AM

  mga sir patulong naman po sa vb.net nag gawa po kasi ako ng project na may MySQL database.
  natapos ko na po siya, kaya lang pano ko po ba gagawin na hindi ko na kailangan iopen yung wamp server ko para mka connect sa database.
  
  ang alam ko po sa MsSQL yun yung dnedetach yung database. pano po ba yun pag sa MySQL?

• admin_password on March 11, 2013 03:05 PM

  mga sir patulong naman po sa vb.net nag gawa po kasi ako ng project na may MySQL database.
  natapos ko na po siya, kaya lang pano ko po ba gagawin na hindi ko na kailangan iopen yung wamp server ko para mka connect sa database.

  
  
  sir download ka ng standalone na mysql server <click here for link>
  
  -- edited by admin_password on Mar 11 2013, 03:11 PM

• Steampack on March 11, 2013 03:28 PM

  Patulong po. Self-study lang po sa sql
  
  I have 2 tables: A and B
  I want to update B.till to 100 where A.id = B.id
  
  First trial
  UPDATE B
  SET B.till = 100
  FROM B
  JOIN A
  ON A.id = B.id
  
  Second trial
  UPDATE B
  SET B.till =100
  FROM A, B
  WHERE A.id = B.id
  
  Ang error sa both trial ay
  Invalid Use of keyword
  Token: FROM
  Line Number: 3
  
  Any advice po?
  The database is Paradox Corel

  
  
  try mo ito :)
  
  update B b
  set b.till = 100
  from A a, B b
  where a.id = b.id
  ;

• brian_smetham on March 16, 2013 10:24 PM

  @all
  
  tanong lang po ano po ba ang pinagkaiba ng sql server at saka sql using xampp po?? and apno po ba gamitin sql server?? TIA.

• arthurjr07 on March 18, 2013 08:57 AM

  @brian_smetham
  
  paki-elaborate kung ano yung gagawin mo.

• brian_smetham on March 18, 2013 11:17 PM

  wala po as of now gusto ko lang pong matutunan mostly san po ba talaga magandang pag praktisan para mahasa din po sa sql?

• brian_smetham on March 18, 2013 11:23 PM

  e diyan po sa mga post dito ano po bang sql ang ginagamit niyo?

• crimson_carp on March 19, 2013 12:04 AM

  hi po, need ko sana help niyo mga sirs.
  nagtatry kasi ako gumawa ng simple data insert/update/delete ng records sa table from a .csv file.
  
  eto po yung code ko,
  right now ang error niya is No Data Found.
  may mali po ba,hehe
  
  <click here for link>

• arthurjr07 on March 19, 2013 07:33 AM

  e diyan po sa mga post dito ano po bang sql ang ginagamit niyo?
  

  
  
  halo halo dito. Meron MsSQLServer, Oracle, MySQL, etc.
  
  yung SQL Server, microsoft ang gumawa nito
  yung Oracle, oracle corp ang gumaga
  yung MySQL, open source ito. Sun microsystem ang gumawa pero nabili na ito ng Oracle. Ewan ko lang kung open source pa rin ito.

• ghikid on April 01, 2013 02:46 PM

  hello mga sir may need lng akong help pano ko ilalagay as a where condition ang isang distinct query.
  
  for example ito ung main query ko
  
  select column A from table A
  
  tapos meron akong select distinct query na need ko ilagay sa where condition
  
  select distinct column B, column C, column D from table A
  where column E=20

• tangangtanga on April 08, 2013 12:45 PM

  Hi mga sir newbie here need help sana ,
  
  Bale heto po un scenario meron akong 2 unrelated tables na meron isang different field name (ex tblHireVehicle.AgreementNo, tblOwnVehicle.AssetNo) the rest nun field pareparehas na (ex BaroconCtrlNo, EquipmentType and so on) gusto ko po sanang makuha as output is un dalawang table mapagsasama niya sa iisang table un mga may magkakaparehong field names say,
  
  1st Table tblHireVehicle
  BaroconCtrlNo || Maker || Model || Capacity || RegistrationNo ||
  HE-006 || Toyota || Camry || 1.8CC || 75813 ||
  
  2nd Table tblOwnVehicle
  BaroconCtrlNo || Maker || Model || Capacity || RegistrationNo ||
  OPAA1-LJP-001 || Hyundai || Santa fe || 2.4CC || 29801 ||
  
  3rd Table tblOutput
  BaroconCtrlNo || Maker || Model || Capacity || RegistrationNo ||
  HE-006 || Toyota || Camry || 1.8CC || 75813 ||
  OPAA1-LJP-001 || Hyundai || Santa fe || 2.4CC || 29801 ||
  
  Thanks in advance pasensya na po newbie here. Gusto ko po sanang umunlad pa ang aking nalalaman sa MS Access meron po ba kayong marerecomendang tutorials para matulungan po ako sa aking pagaaral? salamat ng marami.
  
  Edit:
  
  I tried UNION command para sa problem na to okay siya nakukuha niya un gusto ko kaso ang problema nagkaron ng parameter query dun sa ibang fields since lookup lang siya sa ibang tables. may way po ba para makuha niya un exact value niya dun sa table? o dapat kong tanggalin un mga look up tables at key in nalang gagawin ko. salamat ng marami.
  
  Edit:
  
  Nailed.
  
  -- edited by tangangtanga on Apr 08 2013, 01:37 PM

• watari2010 on April 08, 2013 01:40 PM

  Need help mga sir!
  
  meron ako table sales report. mga fields customer, wk01Qty, wk02Qty...wk52Qty (for 52 weeks of the year)
  problem need ko makuha current week + 4 weeks backward. ex: current month is april week 15 for 04/08/13 (1st week of jan is wk01, 2nd week of jan is wk02 and so on)
  need ko ito sa crystal report. may parameter sa crystal report where in I could input the current week (15)
  
  cur_wk is the name of may parameter
  
  result shoudl be
  customer, wk15, wk15qty, wk14qty, qk13qty,wk12qty, wk11qty
  guys ung wk??qty are dynamic fields. nag iiba sya depende kung ano current week (input). pwede rin kasi mag backtract. pwede input ng user current week is 10 para makita ung past sales.

1082 active users within the last minute, 651 members, 431 guests.
Our newest member is roeline85
Click here to see online members.