update, insert - 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

SQL Server 2005 Forums

Transact-SQL (2005)

update, insert

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-05 : 07:27:48

Hello,
There are thousand of records in tblMain and I am showing a few of those below...
There are many records which are identical except for their Currency field.
For every duplicates except the currency field, I would like to set the Active field to 1 and then create one similar record which has both the currencies and set the Active to 1
Please see below...
Can you please let me know how to get to the result?
Thanks

declare @tblMain table
(
Code varchar(20),
MyID smallint,
Name varchar(20),
process varchar(20)
currency1 varchar(20)
currency2 varchar(20),
Active bit
)

insert into @tblMain(Code, MyID, Name, process, currency1, currency2, Active)
select '0700099108636', 1, 'john', 'sab', 'USD', 1
union
select '0700099108636', 1, 'john', 'sab', 'GBP', 1
union
select '0700099120616', 1, 'jo', 'asa, 'EUR', 1
union
select '0700099120616', 1, 'jo', 'asa, 'RUB', 1
union
select '6800066728914', 1, 'Dave', 'kwa', 'GBP', 1
union
select '6800066728914', 1, 'Dave', 'kwa', 'USD', 1
union
select '123456', 1, 'Dave', 'kwa', 'USD', 1
union
select '123456', 1, 'Dave', 'kwa', 'PLN', 1
union
select '123456', 1, 'Dave', 'kwa', 'PLN', 1
union
select '123456', 1, 'Dave', 'kwa', 'USD', 1

select * from @tblMain

--RESULT

select '0700099108636', 1, 'john', 'sab', 'USD', NULL, 0
union
select '0700099108636', 1, 'john', 'sab', 'GBP', NULL, 0
union
select '0700099108636', 1, 'john', 'sab', 'USD', 'GBP', 1
union
select '0700099120616', 1, 'jo', 'asa', 'EUR', NULL, 0
union
select '0700099120616', 1, 'jo', 'asa', 'RUB', NULL, 0
union
select '0700099120616', 1, 'jo', 'asa', 'EUR', 'RUB', 1
union
select '6800066728914', 1, 'Dave', 'kwa', 'GBP', NULL, 0
union
select '6800066728914', 1, 'Dave', 'kwa', 'USD', NULL, 0
union
select '6800066728914', 1, 'Dave', 'kwa', 'GBP', 'USD', 1
union
select '123456', 1, 'Dave', 'kwa', 'USD', NULL, 0
union
select '123456', 1, 'Dave', 'kwa', 'PLN', NULL, 0
union
select '123456', 1, 'Dave', 'kwa', 'PLN', NULL, 0
union
select '123456', 1, 'Dave', 'kwa', 'USD', NULL, 0
union
select '123456', 1, 'Dave', 'kwa', 'USD', 'PLN', 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 09:14:47

do you mean this?


SELECT Code, MyID, Name, process, currency1,currency2,Active
FROM
(
SELECT Code, MyID, Name, process, currency1,CAST(NULL AS varchar(10)) AS currency2,0 AS Active
FROM YourTable

UNION ALL

SELECT Code, MyID, Name, process, MIN(currency1),MAX(currency1),1
FROM YourTable
GROUP BY Code, MyID, Name, process
)t
ORDER BY Code, MyID, Name, process,Active

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-05 : 09:43:59

Not quite because there has to be an update and inisert as mentined before.
Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 09:53:14

quote:
Originally posted by arkiboys

Not quite because there has to be an update and inisert as mentined before.
Thank you

update and insert onto table you mean?

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-05 : 10:10:55

indeed. See the first post:
"set the Active field to 1 and then create one similar record "
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 10:50:16

[code]
UPDATE t
SET t.Active=0
FROM Table t
INNER JOIN (SELECT Code, MyID, Name, process
FROM YourTable
GROUP BY Code, MyID, Name, process
HAVING COUNT(DISTINCT currency1) > 1)t1
ON t1.Code=t.Code
AND t1.MyID=t.MyID
AND t1.Name=t.Name
AND t1.process = t.process

INSERT INTO TAble
SELECT Code, MyID, Name, process, MIN(currency1),MAX(currency1),1
FROM YourTable
GROUP BY Code, MyID, Name, process
HAVING COUNT(DISTINCT currency1) > 1
[/code]

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-05 : 10:55:07

What do you mean by Table and YourTable please?
The table which I am reading, updating and inserting into can simply have an update of say
update @tblMain set Active = 8 where ID = ?
And then the insert is required for the ones which have been updated (i.e. a similar record to be added but with Active set to 1)
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 10:58:53

quote:
Originally posted by arkiboys

What do you mean by Table and YourTable please?
The table which I am reading, updating and inserting into can simply have an update of say
update @tblMain set Active = 8 where ID = ?
And then the insert is required for the ones which have been updated (i.e. a similar record to be added but with Active set to 1)
Thanks

just put your table name thats it
my suggestion does both update and insert bits
you need to do update only if you've multiple records existing for Code, MyID, Name, process group (as per your sample data)

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-05 : 11:03:19

ok, do you mean to put mytablename into your sql where it says Table and Yourtable ?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 11:08:00

quote:
Originally posted by arkiboys

ok, do you mean to put mytablename into your sql where it says Table and Yourtable ?
Thanks

yup. exactly

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-05 : 14:26:16

thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-06 : 05:45:46

welcome

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-06 : 15:28:26

hi visakh16,
What if you have several records and you want to get the currencies which ar edifferent for that code.
In your sql you have put min and max but what if there are cases where the max currency may be as same as the min currency? I only want to place the second currency which is different to the first one into the currency2?
Should this be done in multiple temp tables to get to the result?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-07 : 10:32:32

quote:
Originally posted by arkiboys

hi visakh16,
What if you have several records and you want to get the currencies which ar edifferent for that code.
In your sql you have put min and max but what if there are cases where the max currency may be as same as the min currency? I only want to place the second currency which is different to the first one into the currency2?
Should this be done in multiple temp tables to get to the result?
Thanks

then put a case when


INSERT INTO TAble
SELECT Code, MyID, Name, process, MIN(currency1),CASE WHEN MAX(currency1) <> MIN(currency1) THEN MAX(currency1) ELSE NULL END,1
FROM YourTable
GROUP BY Code, MyID, Name, process
HAVING COUNT(DISTINCT currency1) > 1

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-08 : 02:59:48

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-08 : 04:36:18

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources